Umer Saeed 
Sr. RF Planning and Optimization Engineer
BSc Telecommunications Engineering, MS Data Science
F2017313014@umt.edu.pk
https://www.linkedin.com/in/engumersaeed/
https://github.com/umersaeed81h
14-Aug-2019
Python for Data Science
Table of contents¶
Chapter 11: Data Frame in Python
- 11.1 What is Data Science?
- 11.2 Data Frames
- 11.3 Library Highlights
- 11.4 Pandas Documentation
- 11.5 Import Python Library
- 11.6 Pandas Version
- 11.7 Create Data Frame
- 11.8 Import Data Frame in Pandas
- 11.9 Export Data Frame From Pandas
- 11.10 Convert the DataFrame to a dictionary
- 11.11 Convert the DataFrame to a array
- 11.12 General Operations on Pandas Data Frames
- 11.13 Data Type
- 11.14 Summary of the Missing Values
- 11.15 Statistical Summary of the Data Frame
- 11.16 Pandas and Pandas Profiling to analyze data
- 11.17 Slicing Subsets of Rows and Columns in Pandas
- 11.18 Conditional Filtering in Pandas
- 11.19 Arithmetic Operations
- 11.20 logical operations
- 11.21 Miscellaneous Operations
- 11.22 8 ways to apply LEFT RIGHT MID in Pandas
- 11.23 Missing data handling
- 11.24 Replace values methods
- 11.25 Varibale types handling
- 11.26 CONCATENATE
- 11.27 Merge Dataframes
- 11.28 Pivot
- 11.29 Pivot Table
- 11.30 Grouper
- 11.31 Melt
- 11.32 Crosstab
- 11.33 groupby
- 11.34 get_group
- 11.35 Transform Function in Pandas
- 11.36 Remove duplicate rows in pandas
- 11.37 Pandas Stack
- 11.38 Pandas Unstack
- 11.39 Python Pandas replicate rows in dataframe
- 11.40 Data manipulations
- 11.41 Data Frame Formatting in Pandas
- 11.42 Time Series Analysis
Chapter 11: Data Frame in Python¶
11.1 What is Data Science?¶
- Data Science or Data Analytics is a process of analyzing large set of data points to get answer on questions related to that data set.
- Pandas is a python module that makes data science easy and effective
11.2 Data Frames¶
Dataframe is a main object in Pandas. It is used to represent data with rows and columnns (tabular or excel spreadsheed like data).
A data frame is a table or a two-dimensional array-like structure in which each column contains values of one variable and each row contains one set of values from each column.
Following are the characteristics of a data frame:
- There can be multiple rows and columns in the data.
- Each row represents a sample of data,
- Each column contains a different variable that describes the samples (rows).
- The data in every column is usually the same type of data – e.g. numbers, strings, dates.
- Unlike matrices, data frames can store different classes of objects in each column;matrices must have every element be the same class.
11.3 Library Highlights¶
A fast and efficient DataFrame object for data manipulation with integrated indexing.
Tools for reading and writing data between in-memory data structures and different formats:
- CSV and text files.
- Microsoft Excel.
- SQL databases.
- The fast HDF5 format.
- etc.
- Intelligent data alignment and integrated handling of missing data: gain automatic label-based alignment in computations and easily manipulate messy data into an orderly form.
- Flexible reshaping and pivoting of data sets.
- Intelligent label-based slicing, fancy indexing, and subsetting of large data sets.
- Columns can be inserted and deleted from data structures for size mutability.
- Aggregating or transforming data with a powerful group by engine allowing split-apply-combine operations on data sets.
- High performance merging and joining of data sets.
- Hierarchical axis indexing provides an intuitive way of working with high-dimensional data in a lower-dimensional data structure.
- Time series-functionality: date range generation and frequency conversion, moving window statistics, moving window linear regressions, date shifting and lagging. Even create domain-specific time offsets and join time series without losing data
- Highly optimized for performance, with critical code paths written in Cython or C.
- Python with pandas is in use in a wide variety of academic and commercial domains, including Finance, Neuroscience, Economics, Statistics, Advertising, Web Analytics, and more.
11.4 Pandas Documentation¶
In [1]:
from IPython.display import YouTubeVideo
YouTubeVideo('CmorAWRsCAw',width=900, height=500)
Out[1]:
In [2]:
from IPython.display import YouTubeVideo
YouTubeVideo('F6kmIpWWEdU',width=900, height=500)
Out[2]:
11.5 Import Python Library¶
In [3]:
import pandas as pd
import numpy as np
import os
import glob
from glob import glob
11.6 Pandas Version¶
In [4]:
pd.__version__
Out[4]:
- But if you also need to know the versions of pandas' dependencies, you can use the show_versions() function:
In [5]:
pd.show_versions()
11.7 Create Data Frame¶
In [6]:
from IPython.display import YouTubeVideo
YouTubeVideo('-Ov1N1_FbP8',width=900, height=500)
Out[6]:
There are many ways to do this, but my favorite ways are;
In [7]:
from IPython.display import YouTubeVideo
YouTubeVideo('3k0HbcUGErE',width=900, height=500)
Out[7]:
1- Create Data Frame Using Dictionary¶
Format-1¶
In [8]:
weather=pd.DataFrame({
'Day':['1/1/2019','1/2/2019','1/3/2019','1/4/2019'],
'Temperature':[30,32,34,36],
'Windspeed':[6,7,10,12],
'Event':['Rain','Sunny','Rain','Sunny']
})
weather
Out[8]:
Format-2 (from_dict)¶
- More information
- Construct DataFrame from dict of array-like or dicts.
In [9]:
weather=pd.DataFrame.from_dict({
'Day':['1/1/2019','1/2/2019','1/3/2019','1/4/2019'],
'Temperature':[30,32,34,36],
'Windspeed':[6,7,10,12],
'Event':['Rain','Sunny','Rain','Sunny']
})
weather
Out[9]:
2- Create Data Frame Using List of Tuple¶
Format-1¶
In [10]:
weather=pd.DataFrame([
('1/1/2019',30,6,'Rain'),
('1/2/2019',32,7,'Sunny'),
('1/3/2019',34,10,'Rain'),
('1/4/2019',36,12,'Sunny')],
columns=["Day","Temperature","Windspeed","Event"])
weather
Out[10]:
Format-2 (from_records)¶
- More information
- Convert structured or record ndarray to DataFrame
In [11]:
sales = pd.DataFrame.from_records([('Jones LLC', 150, 200, 50),
('Alpha Co', 200, 210, 90),
('Blue Inc', 140, 215, 95)],
columns = ['account', 'Jan', 'Feb', 'Mar'])
sales
Out[11]:
3- Create Data Frame Using List of Dictionary¶
Format-1¶
In [12]:
weather=pd.DataFrame([
{'Day':'1/1/2019','Temperature':30,'Windspeed':6,'Event':'Rain'},
{'Day':'1/2/2019','Temperature':32,'Windspeed':7,'Event':'Sunny'},
{'Day':'1/3/2019','Temperature':34,'Windspeed':8,'Event':'Sunny'},
{'Day':'1/4/2019','Temperature':36,'Windspeed':8,'Event':'Rain'}
])
weather
Out[12]:
Format-2¶
In [13]:
sales = pd.DataFrame.from_dict(
[{'account': 'Jones LLC', 'Jan': 150, 'Feb': 200, 'Mar': 140},
{'account': 'Alpha Co', 'Jan': 200, 'Feb': 210, 'Mar': 215},
{'account': 'Blue Inc', 'Jan': 50, 'Feb': 90, 'Mar': 95 }]
)
sales
Out[13]:
Solution-1¶
In [14]:
sales = sales[['account', 'Jan', 'Feb', 'Mar']]
sales
Out[14]:
Solution-2 (from_dict)¶
- Alternatively you could create your dictionary using python’s OrderedDict .
In [15]:
from collections import OrderedDict
sales = pd.DataFrame.from_dict(
OrderedDict([('account', ['Jones LLC', 'Alpha Co', 'Blue Inc']),
('Jan', [150, 200, 50]),
('Feb', [200, 210, 90]),
('Mar', [140, 215, 95]) ])
)
sales
Out[15]:
Solution-3 (from_items)¶
- More information
- (DEPRECATED) Construct a DataFrame from a list of tuples.
In [16]:
sales = pd.DataFrame.from_items(
[('account', ['Jones LLC', 'Alpha Co', 'Blue Inc']),
('Jan', [150, 200, 50]),
('Feb', [200, 210, 90]),
('Mar', [140, 215, 95]),
])
sales
Out[16]:
4- Create Data Frame Using Random Number¶
In [17]:
df= pd.DataFrame(np.random.rand(4, 8))
df
Out[17]:
5- Create a DataFrame from the clipboard¶
- More Information
- Let's say that you have some data stored in an Excel spreadsheet or a Google Sheet, and you want to get it into a DataFrame as quickly as possible.
- Just select the data and copy it to the clipboard. Then, you can use the read_clipboard() function to read it into a DataFrame:
In [18]:
df = pd.read_clipboard()
df
Out[18]:
- Keep in mind that if you want your work to be reproducible in the future, read_clipboard() is not the recommended approach.
empty data frame¶
- Indicator whether DataFrame is empty.
- True if DataFrame is entirely empty (no items), meaning any of the axes are of length 0.
- More Information
In [19]:
df=pd.DataFrame({'A' : []})
df
Out[19]:
- If we only have NaNs in our DataFrame, it is not considered empty! We will need to drop the NaNs to make the DataFrame empty
In [20]:
df = pd.DataFrame({'A' : [np.nan]})
df
Out[20]:
In [21]:
df=df.empty
df
Out[21]:
11.8 Import Data Frame in Pandas¶
In [22]:
from IPython.display import YouTubeVideo
YouTubeVideo('5_QXMwezPJE',width=900, height=500)
Out[22]:
We can import Following Format Files in Pandas
1- Create Data Frame Using *.csv File¶
Example-1¶
In [23]:
df= pd.read_csv('Weather.csv')
df
Out[23]:
Solution-1::Skip Row¶
In [24]:
df= pd.read_csv('Weather.csv',skiprows=1)
df
Out[24]:
Solution-2::Define Header Row¶
In [25]:
df= pd.read_csv('Weather.csv',header=1)
df
Out[25]:
Example-2¶
In [26]:
df= pd.read_csv('Weather_header.csv')
df
Out[26]:
Solution-1::Header set to None¶
In [27]:
df= pd.read_csv('Weather_header.csv',header=None)
df
Out[27]:
Solution-2::Set Header Name¶
In [28]:
df= pd.read_csv('Weather_header.csv',
names=['Date','Temperature',
'Windspeed','Event'])
df
Out[28]:
Set Header Name using list Method¶
In [29]:
df= pd.DataFrame(np.random.rand(4, 8),columns=list('abcdefgh'))
df
Out[29]:
Example-3¶
Import Only Few Rows From *.csv File¶
In [30]:
df= pd.read_csv('Weather1.csv',nrows=2)
df
Out[30]:
Import Only Few Colums Using Variable Name¶
In [31]:
df= pd.read_csv('Weather1.csv',usecols=['Date','Temperature'])
df
Out[31]:
Import Only Few Colums Using Variable Index¶
In [32]:
df= pd.read_csv('Weather1.csv',usecols=[0,3])
df
Out[32]:
Example 4¶
In [33]:
df= pd.read_csv('Family.csv')
df
Out[33]:
Solution::Skip Footer¶
In [34]:
df= pd.read_csv('Family.csv',skipfooter=1)
df
Out[34]:
In [35]:
df= pd.read_csv('Family.csv',skipfooter=1,engine='python')
df
Out[35]:
Example 5¶
In [36]:
from IPython.display import YouTubeVideo
YouTubeVideo('0uBirYFhizE',width=900, height=500)
Out[36]:
In [37]:
df= pd.read_csv('check.csv')
df
Out[37]:
Solution1::Assign Columns Name¶
In [38]:
df.columns = ['Name', 'Student ID','Marks']
df
Out[38]:
Solution2:: Rename¶
- More Information
- Alter axes labels
In [39]:
df= pd.read_csv('check.csv')
df.rename(columns={"Unnamed: 1":"Student ID"})
Out[39]:
Rename Column Name using Replace Method¶
- More Information
- Replace values given in to_replace with value.
In [40]:
df = pd.DataFrame({'col one':[100, 200], 'col two':[300, 400]})
df
Out[40]:
In [41]:
df.columns = df.columns.str.replace(' ', '_')
df
Out[41]:
Remame Column Name using add_prefix() Method¶
- More Information
- Prefix labels with string prefix.
In [42]:
df = pd.DataFrame({'col one':[100, 200], 'col two':[300, 400]})
df.add_prefix('X_')
Out[42]:
Remame Column Name using add_suffix() Method¶
- More Information
- Suffix labels with string suffix.
In [43]:
df = pd.DataFrame({'col one':[100, 200], 'col two':[300, 400]})
df.add_suffix('_Y')
Out[43]:
Import File from url¶
In [44]:
df= pd.read_html('https://github.com/justmarkham/pandas-videos/blob/master/data/titanic_test.csv')
In [45]:
type(df)
Out[45]:
In [46]:
df=df[0]
In [47]:
df.head()
Out[47]:
In [48]:
df=df.iloc[:,1:]
df.head()
Out[48]:
2- Create Data Frame Using *.tsv File¶
In [49]:
df = pd.read_table('chipotle.tsv')
df.head()
Out[49]:
In [50]:
df = pd.read_csv('chipotle.tsv',sep='\t')
df.head()
Out[50]:
3- read table from url¶
Example-1¶
In [51]:
df = pd.read_html('https://en.wikipedia.org/wiki/Pakistan',attrs={'class': 'wikitable'})
In [52]:
len(df)
Out[52]:
In [53]:
df[0]
Out[53]:
Example-2¶
In [54]:
import pandas as pd
df = pd.read_html('https://en.wikipedia.org/wiki/Pakistan')
In [55]:
len(df)
Out[55]:
In [56]:
df[10]
Out[56]:
Example-2a¶
In [57]:
df = pd.read_html('https://en.wikipedia.org/wiki/Pakistan', attrs={"class":"sortable wikitable"})
In [58]:
len(df)
Out[58]:
In [59]:
df[0]
Out[59]:
Example-2b¶
In [60]:
df = pd.read_html('https://en.wikipedia.org/wiki/Pakistan', match='Corporations')
In [61]:
len(df)
Out[61]:
In [62]:
df[0]
Out[62]:
Example-3¶
In [63]:
df = pd.read_html('https://en.wikipedia.org/wiki/List_of_UFC_events',attrs={"id":"Scheduled_events"})
In [64]:
len(df)
Out[64]:
In [65]:
df[0].head()
Out[65]:
Example-4¶
In [66]:
df = pd.read_html('https://en.wikipedia.org/wiki/University_of_California,_Berkeley', attrs={"class":"infobox vcard"})
In [67]:
len(df)
Out[67]:
In [68]:
df[0].head()
Out[68]:
Example-5¶
In [69]:
df = pd.read_html('https://en.wikipedia.org/wiki/University_of_California,_Berkeley', attrs={"class":"infobox"})
In [70]:
len(df)
Out[70]:
In [71]:
df[0].head()
Out[71]:
Example-6¶
In [72]:
df = pd.read_html('https://www.esportsearnings.com/players',attrs={"class":"detail_list_table"},header=0)
In [73]:
len(df)
Out[73]:
In [74]:
df[0].head()
Out[74]:
4- Create Data Frame Using *.xlsx File¶
Example-1¶
In [75]:
df=pd.read_excel('WHO_ex.xlsx','WHO')
df.head()
Out[75]:
Get all sheet names¶
In [76]:
df = pd.ExcelFile('Weather_subject.xlsx')
sheet_names = df.sheet_names
sheet_names
Out[76]:
In [77]:
Weather = pd.read_excel(df, 'Weather')
student = pd.read_excel(df, 'Subject')
In [78]:
Weather
Out[78]:
In [79]:
student
Out[79]:
Display all sheets¶
In [80]:
for tab in sheet_names:
print('################################## ' + tab + ' ##################################')
dfall = pd.read_excel(df, tab)
print(dfall)
5- Fixed-width formatted lines¶
In [81]:
df=pd.read_fwf('KPK_Weather.prn',colspecs = [(0,63),(63,76),(76,81),(81,92),(92,102)],parse_dates=["Day"])
df
Out[81]:
6- Import Table From pdf file¶
Install Tabula¶
In [82]:
import sys
!{sys.executable} -m pip install tabula-py
import tabula¶
In [83]:
import tabula
from tabula import read_pdf
In [84]:
df = read_pdf("http://www.uncledavesenterprise.com/file/health/Food%20Calories%20List.pdf", pages='all', multiple_tables=True)
In [85]:
len(df)
Out[85]:
In [86]:
df[4].head()
Out[86]:
11.9 Export Data Frame From Pandas¶
In [87]:
from IPython.display import YouTubeVideo
YouTubeVideo('-0NwrcZOKh',width=900, height=500)
Out[87]:
We can Export Following Format Files in Pandas
Data Frame-1¶
In [88]:
student=pd.DataFrame({
'Name':['Umer','Ali','Ahmed','Abdullah'],
'Marks':[100,98,97,95],
'Result':['Pass','Pass','Pass','Pass'],
'Subject':['Math','Phy','Chem','Bio']
})
student
Out[88]:
Data Frame-2¶
In [89]:
weather=pd.DataFrame({
'day':['1/1/2019','1/2/2019','1/3/2019','1/4/2019'],
'temperature':[30,32,34,36],
'windspeed':[6,7,10,12],
'event':['Rain','Sunny','Rain','Sunny']
})
weather
Out[89]:
1- Write .csv Format File¶
- More Information
- Write DataFrame to a comma-separated values (csv) file
In [90]:
student.to_csv('Output1.csv')
Export without Index¶
In [91]:
student.to_csv('output2.csv',index=False)
Export (without Index)/Selected Columns¶
In [92]:
student.to_csv('output3.csv',
index=False,columns=['Name','Marks'])
Export File (Skip Header)¶
In [93]:
student.to_csv('output4.csv',index=False,header=False)
2- Write .xlsx Format File¶
- More Information
- Write DataFrame to an excel sheet
In [94]:
student.to_excel("output5.xlsx",sheet_name="Student")
Export Without Index, specific row and col no¶
In [95]:
student.to_excel("output6.xlsx",sheet_name="Student",
startrow=3,startcol=3,index=False)
Export multiple worksheets in the same workbook¶
In [96]:
with pd.ExcelWriter('output7.xlsx') as writer:
weather.to_excel(writer,sheet_name="Weather",index=False)
student.to_excel(writer,sheet_name="Subject",index=False)
Read Write Data From Database¶
In [97]:
from IPython.display import YouTubeVideo
YouTubeVideo('M-4EpNdlSuY',width=900, height=500)
Out[97]:
11.10 Convert the DataFrame to a dictionary¶
to_dict¶
In [98]:
df= pd.read_csv('Family.csv',skipfooter=1,engine='python')
df
Out[98]:
In [99]:
df.to_dict('series')
Out[99]:
In [100]:
df.to_dict('split')
Out[100]:
In [101]:
df.to_dict('index')
Out[101]:
In [102]:
from collections import OrderedDict, defaultdict
df.to_dict(into=OrderedDict)
Out[102]:
In [103]:
df.to_dict('records', into=defaultdict(list))
Out[103]:
blocks¶
- (Deprecated since version 0.21.0.) DataFramem blocks attribute has successfully returned a dictionary containing the data of the dataframe.
- Homogeneous columns are places in the same block.
In [104]:
df= pd.read_csv('Family.csv',skipfooter=1,engine='python')
df
Out[104]:
In [105]:
df.blocks
Out[105]:
11.11 Convert the DataFrame to a array¶
to_records()¶
- Convert DataFrame to a NumPy record array
In [106]:
df= pd.read_csv('Family.csv',skipfooter=1,engine='python')
df
Out[106]:
In [107]:
df.to_records()
Out[107]:
In [108]:
df.to_records(index=False)
Out[108]:
values¶
- More Information
- values Return a Numpy representation of the DataFrame.
- Only the values in the DataFrame will be returned, the axes labels will be removed.
- Warning: Pandas recommend using DataFrame.to_numpy() instead
In [109]:
import pandas as pd
df= pd.read_csv('Family.csv',skipfooter=1,engine='python')
df
Out[109]:
In [110]:
df.values
Out[110]:
to_numpy¶
In [111]:
df.to_numpy()
Out[111]:
array¶
In [112]:
df['Name'].array
Out[112]:
to_list¶
- Return a list of the values.
In [113]:
df = pd.DataFrame({'a':[1,2,3], 'b':[4,5,6]})
df
Out[113]:
In [114]:
df['a'].to_list()
Out[114]:
explode¶
- Transform each element of a list-like to a row, replicating the index values.
Example-1¶
In [115]:
df = pd.Series([[1, 2, 3], 'foo', [], [3, 4]])
In [116]:
df
Out[116]:
In [117]:
df.explode()
Out[117]:
Example-2¶
In [118]:
df=pd.DataFrame({'A':[1,2],'B':[[1,2],[1,2]]})
In [119]:
df
Out[119]:
In [120]:
df.explode('B')
Out[120]:
get_values¶
- More Information
- (DEPRECATED) Return an ndarray after converting sparse values to dense.
In [121]:
df= pd.read_csv('Family.csv',skipfooter=1,engine='python')
df
Out[121]:
In [122]:
df.get_values()
Out[122]:
11.12 General Operations on Pandas Data Frames¶
Head of the Data Frame¶
- More Informaion
- This function returns the first n rows for the object based on position.
- It is useful for quickly testing if your object has the right type of data in it.
In [123]:
df_who= pd.read_csv('WHO_csv.csv')
In [124]:
df_who.head()
Out[124]:
In [125]:
df_who.head(2)
Out[125]:
Tail of the Data Frame¶
- More Information
- Return the last n rows.
In [126]:
df_who.tail()
Out[126]:
In [127]:
df_who.tail(2)
Out[127]:
Sample of the Data Frame¶
- More Information
- Return a random sample of items from an axis of object.
In [128]:
df_who.sample(3)
Out[128]:
Shape of the Data Frame¶
- More Information
- Return a tuple representing the dimensionality of the DataFrame
Method-1¶
In [129]:
df_who.shape
Out[129]:
Method-2¶
In [130]:
rows,columns= df_who.shape
print(rows,columns)
In [131]:
print("No of rows in the WHO Data Set:")
print(rows)
print("No of columns in the WHO Data Set:")
print(columns)
Dimensions of the Data Frame¶
- More Information
- Return an int representing the number of axes / array dimensions.
- Return 1 if Series. Otherwise return 2 if DataFrame.
Example-1¶
In [132]:
df_who.ndim
Out[132]:
Example-2¶
In [133]:
s = pd.Series({'a': 1, 'b': 2, 'c': 3})
s
Out[133]:
In [134]:
s.ndim
Out[134]:
Size of the Data Frame¶
- More Information
- Number of elements in the array.
Example-1¶
In [135]:
df_who.size
Out[135]:
Example-2¶
In [136]:
s = pd.Series({'a': 1, 'b': 2, 'c': 3})
s
Out[136]:
In [137]:
s.size
Out[137]:
Get Variables Name of the Data Frame¶
Method-1¶
- The column labels of the DataFrame.
In [138]:
df_who.columns
Out[138]:
Method-2¶
In [139]:
df_who.keys()
Out[139]:
Method-3¶
In [140]:
df_who['Country'].name
Out[140]:
Index of the Data Frame¶
- The index (row labels) of the DataFrame.
In [141]:
df_who.index
Out[141]:
axes of the Data Frame¶
- More Information
- Return a list representing the axes of the DataFrame.
- It has the row axis labels and column axis labels as the only members.
- They are returned in that order.
In [142]:
df_who.axes
Out[142]:
Set Index to the specfic Column¶
- More Information
- Set the DataFrame index (row labels) using one or more existing columns
Example-1¶
In [143]:
from IPython.display import YouTubeVideo
YouTubeVideo('XaCSdr7pPmY',width=900, height=500)
Out[143]:
In [144]:
df_who.set_index('Region',inplace=True)
df_who
Out[144]:
Example-2¶
In [145]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.set_index(['Region','Country'],inplace=True)
df_who
Out[145]:
Re-Set Index¶
In [146]:
df_who.reset_index(['Region','Country'],inplace=True)
In [147]:
df_who.head()
Out[147]:
set_axis¶
- Assign desired index to given axis.
- Indexes for column or row labels can be changed by assigning a list-like or Index
- More Information
In [148]:
df = pd.DataFrame({"A": [1, 2, 3], "B": [4, 5, 6]})
df
Out[148]:
In [149]:
df.set_axis(['a', 'b', 'c'], axis='rows', inplace=True)
df
Out[149]:
In [150]:
df.set_axis(['A1','B1'], axis='columns', inplace=True)
df
Out[150]:
Why do some pandas commands end with parentheses (and others don't)?¶
In [151]:
from IPython.display import YouTubeVideo
YouTubeVideo('hSrDViyKWVk',width=900, height=500)
Out[151]:
11.13 Data Type¶
dtype¶
- More Information
- Return the dtypes in the DataFrame.
In [152]:
df_who= pd.read_csv('WHO_csv.csv')
In [153]:
df_who.dtypes
Out[153]:
In [154]:
df_who['Country'].dtype
Out[154]:
In [155]:
df_who['Country'].dtypes
Out[155]:
get_dtype_counts()¶
- More Information
- (DEPRECATED) Return counts of unique dtypes in this object.
In [156]:
df_who.get_dtype_counts()
Out[156]:
infer_objects¶
- More Information
- Attempt to infer better dtypes for object columns.
- Attempts soft conversion of object-dtyped columns, leaving non-object and unconvertible columns unchanged.
- The inference rules are the same as during normal Series/DataFrame construction.
In [157]:
df = pd.DataFrame({"A": ["a", 1, 2, 3]})
df
Out[157]:
In [158]:
df = df.iloc[1:]
df
Out[158]:
In [159]:
df.dtypes
Out[159]:
In [160]:
df.infer_objects().dtypes
Out[160]:
Does your object column contain mixed data types?¶
In [161]:
df = pd.DataFrame({"A": ["a", 1, 2, 3]})
df
Out[161]:
In [162]:
df.dtypes
Out[162]:
In [163]:
df.A.apply(type)
Out[163]:
In [164]:
df.A.apply(type).value_counts()
Out[164]:
In [165]:
from IPython.display import YouTubeVideo
YouTubeVideo('V0AWyzVMf54',width=900, height=500)
Out[165]:
Convert strings to numbers¶
In [166]:
df = pd.DataFrame({'col_one':['1.1', '2.2', '3.3'],
'col_two':['4.4', '5.5', '6.6'],
'col_three':['7.7', '8.8', '-']})
df
Out[166]:
- These numbers are actually stored as strings, which results in object columns:
In [167]:
df.dtypes
Out[167]:
- In order to do mathematical operations on these columns, we need to convert the data types to numeric. You can use the astype() method on the first two columns:
- More Information
In [168]:
df.astype({'col_one':'float', 'col_two':'float'}).dtypes
Out[168]:
- However, this would have resulted in an error if you tried to use it on the third column, because that column contains a dash to represent zero and pandas doesn't understand how to handle it.
- Instead, you can use the to_numeric() function on the third column and tell it to convert any invalid input into NaN values:
In [169]:
pd.to_numeric(df.col_three, errors='coerce')
Out[169]:
- If you know that the NaN values actually represent zeros, you can fill them with zeros using the fillna() method:
In [170]:
pd.to_numeric(df.col_three, errors='coerce').fillna(0)
Out[170]:
- Finally, you can apply this function to the entire DataFrame all at once by using the apply() method:
In [171]:
df1=df.apply(pd.to_numeric, errors='coerce').fillna(0)
df1
Out[171]:
In [172]:
df1.dtypes
Out[172]:
- This one line of code accomplishes our goal, because all of the data types have now been converted to float:
Example-1¶
In [173]:
import pandas as pd
df= pd.read_csv('Weather1.csv')
df
Out[173]:
In [174]:
df.dtypes
Out[174]:
In [175]:
df= pd.read_csv('Weather1.csv',dtype = {"Temperature" : "float64","Windspeed" : "float64"})
df
Out[175]:
In [176]:
df.dtypes
Out[176]:
Example-2¶
In [177]:
df= pd.read_csv('Weather1.csv',parse_dates=['Date'],dtype = {"Temperature" : "float64","Windspeed" : "float64"})
df
Out[177]:
In [178]:
df.dtypes
Out[178]:
In [179]:
from IPython.display import YouTubeVideo
YouTubeVideo('P_q0tkYqvSk',width=900, height=500)
Out[179]:
ftypes¶
- More Information
- (DEPRECATED) Return the ftypes (indication of sparse/dense and dtype) in DataFrame.
- This returns a Series with the data type of each column.
- The result’s index is the original DataFrame’s columns.
- Columns with mixed types are stored with the object dtype
In [180]:
df_who.ftypes
Out[180]:
get_ftype_counts()¶
- More Information
- Deprecated since version 0.23.0.
In [181]:
df_who.get_ftype_counts()
Out[181]:
Select columns by data type¶
- More Information
- Return a subset of the DataFrame’s columns based on the column dtypes.
- Let's say you need to select only the numeric columns. You can use the select_dtypes() method:
In [182]:
df_who.select_dtypes(include='number').head()
Out[182]:
- This includes both int and float columns.
In [183]:
df_who.select_dtypes(include='float64').head()
Out[183]:
In [184]:
df_who.select_dtypes(include='int64').head()
Out[184]:
In [185]:
df_who.select_dtypes(include='object').head()
Out[185]:
- You can tell it to include multiple data types by passing a list:
In [186]:
df_who.select_dtypes(include=['int64', 'object']).head()
Out[186]:
- You can also tell it to exclude certain data types:
In [187]:
df_who.select_dtypes(exclude='number').head()
Out[187]:
Date Time Data Type¶
In [188]:
df= pd.read_csv("DateIssue.csv")
df
Out[188]:
In [189]:
type(df.Date[0])
Out[189]:
Solution¶
In [190]:
df= pd.read_csv("DateIssue.csv",parse_dates=['Date'])
df
Out[190]:
In [191]:
type(df.Date[0])
Out[191]:
memory_usage¶
In [192]:
from IPython.display import YouTubeVideo
YouTubeVideo('wDYDYGyN_cw',width=900, height=500)
Out[192]:
In [193]:
df_who.memory_usage()
Out[193]:
In [194]:
df_who.memory_usage(index=False)
Out[194]:
- Use a Categorical for efficient storage of an object-dtype column with many repeated values.
In [195]:
df_who['Region'].astype('category').memory_usage(deep=True)
Out[195]:
In [196]:
df_who['Region'].astype('category').memory_usage(deep=False)
Out[196]:
nbytes¶
In [197]:
df_who['Country'].nbytes
Out[197]:
11.14 Summary of the Missing Values¶
In [198]:
from IPython.display import YouTubeVideo
YouTubeVideo('fCMrO_VzeL8',width=900, height=500)
Out[198]:
Info¶
- More Information
- Print a concise summary of a DataFrame.
- This method prints information about a DataFrame including the index dtype and column dtypes, non-null values and memory usage.
In [199]:
df_who= pd.read_csv('WHO_csv.csv')
In [200]:
df_who.info()
# df_who.info(verbose=True)
In [201]:
df_who.info(verbose=False)
isnull¶
- More Information
- Detect missing values.
In [202]:
df_who.isnull().head()
Out[202]:
In [203]:
df_who.isnull().sum()
Out[203]:
isna¶
- More Information
- Detect missing values.
In [204]:
df_who.isna().head()
Out[204]:
In [205]:
df_who.isna().sum()
Out[205]:
notnull¶
- More Information
- Detect existing (non-missing) values.
In [206]:
df_who.notnull().head()
Out[206]:
In [207]:
df_who.notnull().sum()
Out[207]:
notna¶
- More Information
- Detect existing (non-missing) values
In [208]:
df_who.notna().head()
Out[208]:
In [209]:
df_who.notna().sum()
Out[209]:
hasnans¶
In [210]:
df_who['PrimarySchoolEnrollmentMale'].hasnans
Out[210]:
In [211]:
df_who['Region'].hasnans
Out[211]:
11.15 Statistical Summary of the Data Frame¶
In [212]:
df_who= pd.read_csv('WHO_csv.csv')
Find sum¶
- More Information
- Return the sum of the values for the requested axis
In [213]:
df_who.sum()
Out[213]:
Find count¶
- Count non-NA cells for each column or row.
In [214]:
df_who.count()
Out[214]:
Find Max¶
- More Information
- This method returns the maximum of the values in the object.
In [215]:
df_who.max()
Out[215]:
Find Min¶
- More Information
- This method returns the minimum of the values in the object.
In [216]:
df_who.min()
Out[216]:
Find Mean¶
- More Information
- Return the mean of the values for the requested axis
In [217]:
df_who.mean()
Out[217]:
Find Median¶
- More Information
- Return the median of the values for the requested axis
In [218]:
df_who.median()
Out[218]:
Find Mode¶
In [219]:
df = pd.DataFrame({'A': [2, 2, 1, 2, 1, 2, 3]})
In [220]:
df.mode()
Out[220]:
Find Standard Deviation¶
- More Information
- Return sample standard deviation over requested axis.
In [221]:
df_who.std()
Out[221]:
Find kurtosis¶
- Return unbiased kurtosis over requested axis using Fisher’s definition of kurtosis (kurtosis of normal == 0.0).
- Normalized by N-1
Method-1¶
In [222]:
df_who.kurt()
Out[222]:
Method-2¶
In [223]:
df_who.kurtosis()
Out[223]:
Find Mean absolute deviation (MAD)¶
- More Information
- Return the mean absolute deviation of the values for the requested axis
In [224]:
df_who.mad(axis = 0)
Out[224]:
In [225]:
df_who.mad()
Out[225]:
Find standard error of the mean¶
- More Information
- Return unbiased standard error of the mean over requested axis.
In [226]:
df_who.sem()
Out[226]:
Find variance¶
- More Information
- Return unbiased variance over requested axis.
In [227]:
df_who.var()
Out[227]:
Find skew¶
- More Information
- Return unbiased skew over requested axis Normalized by N-1
In [228]:
df_who.skew()
Out[228]:
Find covariance¶
- More Information
- Compute pairwise covariance of columns, excluding NA/null values
In [229]:
df_who.cov()
Out[229]:
Find correlation¶
- Compute pairwise correlation of columns, excluding NA/null values
- More Information
In [230]:
from IPython.display import YouTubeVideo
YouTubeVideo('sCkS-0kIRCE',width=900, height=500)
Out[230]:
In [231]:
df_who.corr(method='pearson')
Out[231]:
other methods are available in Pandas are;
- pearson : standard correlation coefficient
- kendall : Kendall Tau correlation coefficient
- spearman : Spearman rank correlation
Find corrwith¶
- More Information
- Compute pairwise correlation between rows or columns of two DataFrame objects.
In [232]:
df1 = pd.DataFrame({"A":[1, 5, 7, 8],
"B":[5, 8, 4, 3],
"C":[10, 4, 9, 3]})
In [233]:
df2 = pd.DataFrame({"A":[5, 3, 6, 4],
"B":[11, 2, 4, 3],
"C":[4, 3, 8, 5]})
- To find the correlation among the
- columns of dfcw1 and dfcw2 along the row axis
In [234]:
df1.corrwith(df2, axis = 0)
Out[234]:
In [235]:
df1.corrwith(df2, axis = 1)
Out[235]:
Find quantile¶
In [236]:
df_who.quantile([0.25,0.50,0.75,0.100], numeric_only=True)
Out[236]:
Apply Count Fucntion in the row¶
In [237]:
from IPython.display import YouTubeVideo
YouTubeVideo('PtO3t6ynH-8',width=900, height=500)
Out[237]:
In [238]:
spl2= pd.read_csv('Scopus_1926-1950.csv',encoding='latin-1')
# Preprocessing
# Remove Space between Author(s)ID
spl2['Author(s)ID'] = spl2['Author(s)ID'].str.replace(" ","")
# Remove Author(s)ID end with ;
spl2['Author(s)ID'] = spl2['Author(s)ID'].str.rstrip(';')
spl2 = spl2.join(spl2['Author(s)ID'].str.split(';', expand=True).add_prefix('Author_'))
spl2.head()
Out[238]:
In [239]:
spl2.keys()
Out[239]:
In [240]:
spl2['AuthorCounts']=spl2.iloc[:,19:].count(axis=1)
spl2.head()
Out[240]:
Describe¶
In [241]:
from IPython.display import YouTubeVideo
YouTubeVideo('B-r9VuK80dk',width=900, height=500)
Out[241]:
- More Information
- Generates descriptive statistics that summarize the central tendency, dispersion and shape of a dataset’s distribution, excluding NaN values.
In [242]:
df_who.describe()
Out[242]:
In [243]:
df_who.describe(include='all')
Out[243]:
In [244]:
df_who['Population'].describe()
Out[244]:
In [245]:
df_who[['Population','Over60']].describe()
Out[245]:
In [246]:
df_who.describe(include=['object'])
Out[246]:
In [247]:
df_who.describe().loc['min':'max']
Out[247]:
In [248]:
df_who.describe().loc['min':'max', 'Under15':'ChildMortality']
Out[248]:
11.16 Pandas and Pandas Profiling to analyze data¶
- Let's say that you've got a new dataset, and you want to quickly explore it without too much work. There's a separate package called pandas-profiling that is designed for this purpose.
- First you have to install it using conda or pip. Once that's done, you import pandas_profiling:
- You also install pandas-profiling in Jupter Notebook using following command.
In [249]:
from IPython.display import YouTubeVideo
YouTubeVideo('EaHWjkEPHr8',width=900, height=500)
Out[249]:
Pandas Profiling Installation¶
In [250]:
import sys
!{sys.executable} -m pip install pandas-profiling
Then, simply run the ProfileReport() function and pass it any DataFrame. It returns an interactive HTML report:
- The first section is an overview of the dataset and a list of possible issues with the data.
- The next section gives a summary of each column. You can click "toggle details" for even more information.
- The third section shows a heatmap of the correlation between columns.
- And the fourth section shows the head of the dataset.
In [251]:
import pandas as pd
import pandas_profiling
df_who= pd.read_csv('WHO_csv.csv')
In [252]:
pandas_profiling.ProfileReport(df_who)
Out[252]:
11.17 Slicing Subsets of Rows and Columns in Pandas¶
In [253]:
from IPython.display import YouTubeVideo
YouTubeVideo('zxqjeyKP2Tk',width=900, height=500)
Out[253]:
In [254]:
from IPython.display import YouTubeVideo
YouTubeVideo('OYZNk7Z9s6I',width=900, height=500)
Out[254]:
In [255]:
from IPython.display import YouTubeVideo
YouTubeVideo('xvpNA7bC8cs',width=900, height=500)
Out[255]:
at¶
- More Information
- Access a single value for a row/column label pair.
In [256]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.head()
Out[256]:
In [257]:
df_who.at[1, 'Country']
Out[257]:
In [258]:
df_who.at[3, 'Population']=780
In [259]:
df_who.head()
Out[259]:
- Get value within a Series
In [260]:
df_who.loc[3].at['Population']
Out[260]:
iat¶
- More Information
- Access a single value for a row/column pair by integer position.
- Similar to iloc, in that both provide integer-based lookups.
- Use iat if you only need to get or set a single value in a DataFrame or Series.
- Get value at specified row/column pair
In [261]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.head()
Out[261]:
In [262]:
df_who.iat[0, 0]
Out[262]:
In [263]:
df_who.iat[2, 3]=880
In [264]:
df_who.head()
Out[264]:
- Get value within a series
In [265]:
df_who.loc[0].iat[0]
Out[265]:
ix¶
- A primarily label-location based indexer, with integer position fallback.
- Deprecated
In [266]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.head()
Out[266]:
In [267]:
df_who.ix[10:20, ['Country','Over60']]
Out[267]:
In [268]:
df_who.ix[10:15,]
Out[268]:
In [269]:
df_who.ix[:4, 1:3]
Out[269]:
In [270]:
df_who.ix[[1,3,5], [1,8]]
Out[270]:
filter¶
- Subset rows or columns of dataframe according to labels in the specified index
- More Information-1
- More Information-2
In [271]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.head()
Out[271]:
In [272]:
df_who.filter(['Country', 'CellularSubscribers','Population']).head()
Out[272]:
In [273]:
df_who.filter(regex ='[cC]').head()
Out[273]:
In [274]:
df_who.filter(like ='Enr').head()
Out[274]:
Subset of Each Rows and Columns¶
In [275]:
df_who= pd.read_csv('WHO_csv.csv')
df_who[:]
Out[275]:
Subset of Specific Rows¶
In [276]:
df_who[10:14]
Out[276]:
loc and iloc¶
- loc: Access a group of rows and columns by label(s) or a boolean array.
- iloc: Purely integer-location based indexing for selection by position.
- More Information
Subset Specific Rows(Using iloc)¶
In [277]:
df_who.iloc[10:14]
Out[277]:
Subset Specific Rows(Using loc)¶
In [278]:
df_who.loc[[2,4,6]]
Out[278]:
Subset of Specific Columns(Using iloc)¶
In [279]:
df_who.iloc[:,1:4]
Out[279]:
Subset Specific Rows and Columns (Using iloc)¶
In [280]:
df_who.iloc[14:25, 1:4]
Out[280]:
In [281]:
df_who.Country
Out[281]:
Method-2¶
In [282]:
df_who['Country']
Out[282]:
Subset Specific Column(Using Variable Name)-Mutipule Variable¶
In [283]:
df_who[['Country', 'Region', 'Population']]
Out[283]:
subset Specific Rows and Columns(Using Variable Name & loc)¶
In [284]:
df_who.loc[1:4,['Country', 'Region', 'Population']]
Out[284]:
In [285]:
df_who.loc[[2,4,50,52],['Country', 'Region', 'Population']]
Out[285]:
In [286]:
df_who.head()
Out[286]:
In [287]:
pd.concat([df_who.loc[:,'Country':'Under15'],df_who.loc[:,'FertilityRate'],df_who.loc[:,'ChildMortality':'GNI']],axis='columns').head()
Out[287]:
In [288]:
df_who= pd.read_csv('WHO_csv.csv')
df_who[list(df_who.columns[0:3])+list(df_who.columns[5:7])+list(df_who.columns[8:11])].head()
Out[288]:
In [289]:
df_who.iloc[:,np.r_[0:3,5,9:11]].head()
Out[289]:
Example¶
In [290]:
df_who.loc[df_who.Population>360000,['Country']]
Out[290]:
Example¶
In [291]:
df_who.loc[df_who.Population>360000,['Country','Population']]
Out[291]:
Reverse row order¶
In [292]:
df_who.loc[::-1]
Out[292]:
- What if you also wanted to reset the index so that it starts at zero?
- You would use the reset_index() method and tell it to drop the old index entirely:
- More Information
- For DataFrame with multi-level index, return new DataFrame with labeling information in the columns under the index names, defaulting to ‘level_0’, ‘level_1’, etc.
reset_index()¶
In [293]:
df_who.loc[::-1].reset_index(drop=True)
Out[293]:
- As you can see, the rows are in reverse order but the index has been reset to the default integer index.
Reverse column order¶
In [294]:
df_who.loc[:, ::-1]
Out[294]:
idxmax¶
- Return index of first occurrence of maximum over requested axis.
- NA/null values are excluded.
In [295]:
from IPython.display import YouTubeVideo
YouTubeVideo('egdfGJaBIh0',width=900, height=500)
Out[295]:
In [296]:
df = pd.DataFrame({"A":[4, 5, 2, 6],
"B":[11, 2, 115, 8],
"C":[1, 8, 66, 4]})
df
Out[296]:
In [297]:
#df.idxmax(axis=0)
df.idxmax()
#0='rows'
Out[297]:
In [298]:
df.loc[df.idxmax(axis=0)]
Out[298]:
In [299]:
df.idxmax(axis=1)
#1='column'
Out[299]:
idxmin¶
- Return index of first occurrence of minimum over requested axis.
- NA/null values are excluded.
In [300]:
df = pd.DataFrame({"A":[4, 5, 2, 6],
"B":[11, 2, 115, 8],
"C":[1, 8, 66, 4]})
df
Out[300]:
In [301]:
#df.idxmin(axis=0)
df.idxmin()
#0='rows'
Out[301]:
In [302]:
df.idxmin(axis=1)
#1='colmun'
Out[302]:
first_valid_index¶
- first_valid_index: Return index for first non-NA/null value.
In [303]:
df = pd.DataFrame({
"A1":[np.NaN,np.NaN,np.NaN,5,6,np.NaN,np.NaN],
})
df
Out[303]:
In [304]:
df.first_valid_index()
Out[304]:
last_valid_index¶
- last_valid_index: Return index for last non-NA/null value.
In [305]:
df = pd.DataFrame({
"A1":[np.NaN,np.NaN,np.NaN,5,6,np.NaN,np.NaN],
})
df
Out[305]:
In [306]:
df.last_valid_index()
Out[306]:
transpose¶
- Transpose index and columns.
- More Information-1
- More Information-2
In [307]:
df=pd.read_csv('rep.csv')
df
Out[307]:
Method-1¶
In [308]:
df.T
Out[308]:
Method-2¶
In [309]:
df.transpose()
Out[309]:
xs¶
- Returns a cross-section (row(s) or column(s)) from the Series/DataFrame.
- Defaults to cross-section on the rows (axis=0).
- More Information
In [310]:
df=pd.read_csv('rep.csv')
df.xs(1)
Out[310]:
In [311]:
df.xs('Name',axis=1)
Out[311]:
slice_shift¶
- Equivalent to shift without copying data.
- More Information
In [312]:
df = pd.DataFrame({"A":[1, 2, 3, 4, 5],
"B":[10, 20, 30, 40, 50],
"C":[11, 22, 33, 44, 55],
"D":[12, 24, 51, 36, 2]})
df
Out[312]:
In [313]:
df.slice_shift(2)
Out[313]:
In [314]:
df.slice_shift(2, axis = 0)
Out[314]:
In [315]:
df.slice_shift(2, axis = 1)
Out[315]:
In [316]:
df.slice_shift(-2, axis = 1)
Out[316]:
truncate¶
- Truncate a Series or DataFrame before and after some index value.
- More Information
In [317]:
df = pd.DataFrame({'A': ['a', 'b', 'c', 'd', 'e'],
'B': ['f', 'g', 'h', 'i', 'j'],
'C': ['k', 'l', 'm', 'n', 'o']})
df
Out[317]:
In [318]:
df.truncate(before=2, after=3)
Out[318]:
In [319]:
df.truncate(before="A", after="B", axis="columns")
Out[319]:
In [320]:
df['A'].truncate(before=2, after=4)
Out[320]:
11.18 Conditional Filtering in Pandas¶
In [321]:
from IPython.display import YouTubeVideo
YouTubeVideo('2AFGPdNn4FM',width=900, height=500)
Out[321]:
In [322]:
import pandas as pd
df_who= pd.read_csv('WHO_csv.csv')
Example-1(a)¶
- Filter Population geater than 360000.
Method-1¶
In [323]:
df_who[df_who.Population>360000]
Out[323]:
Method-2¶
In [324]:
df_who[df_who['Population'].gt(360000)]
Out[324]:
Example-1(b)¶
- Filter Population less than 360000.
Method-1¶
In [325]:
df_who[df_who.Population<360000]
Out[325]:
Method2¶
In [326]:
df_who[df_who['Population'].lt(360000)]
Out[326]:
Example-1(c)¶
- Filter Population equal to 28541.
Method-1¶
In [327]:
df_who[df_who.Population==28541]
Out[327]:
Method-2¶
In [328]:
df_who[df_who['Population'].eq(28541)]
Out[328]:
Example-1(d)¶
- Filter Population not equal to 28541.
Method-1¶
In [329]:
df_who[df_who.Population!=28541]
Out[329]:
Method-2¶
In [330]:
df_who[df_who['Population'].ne(28541)]
Out[330]:
Example-1(e)¶
Method-1¶
In [331]:
df_who[df_who.Population>=318000]
Out[331]:
Method-2¶
In [332]:
df_who[df_who['Population'].ge(318000)]
Out[332]:
Example-1(f)¶
Method-1¶
In [333]:
df_who[df_who.Population<=10000]
Out[333]:
Method-2¶
In [334]:
df_who[df_who['Population'].le(10000)]
Out[334]:
Example-2¶
Method-1¶
In [335]:
df_who[df_who.Population==df_who.Population.max()]
Out[335]:
Method-2¶
In [336]:
df_who[df_who.Population==df_who['Population'].max()]
Out[336]:
Note:¶
- Statment-1: df_who[df_who.Population==df_who.Population.max()]
- Statment-2: df_who[df_who.Population==df_who['Population'].max()]
- Statment-1=Statment-2
Example-3¶
In [337]:
from IPython.display import YouTubeVideo
YouTubeVideo('YPItfQ87qjM',width=900, height=500)
Out[337]:
- Filter Population>=20000 and LifeExpectancy=60.
Method-1¶
In [338]:
df_who[(df_who.Population>=20000) & (df_who.LifeExpectancy==60)]
Out[338]:
Method-2¶
In [339]:
df_who[df_who['Population'].ge(20000)& (df_who['LifeExpectancy'].eq(60))]
Out[339]:
Example-4¶
contains¶
In [340]:
df_who[df_who.Country.str.contains('istan')]
Out[340]:
Example-5¶
startswith¶
In [341]:
df_who[df_who['Country'].str.startswith('A')]
Out[341]:
Example-6¶
endswith¶
In [342]:
df_who[df_who['Country'].str.endswith('n')]
Out[342]:
Example-7¶
- Max Population Data Frame (Specific Columns)
In [343]:
df_who[['Region','CellularSubscribers','Population']][df_who.Population==df_who['Population'].max()]
Out[343]:
Example-8¶
unique¶
- Unique Values in a Single Variable
In [344]:
df_who.Region.unique()
Out[344]:
nunique¶
- More Information
- Return Series with number of distinct observations over requested axis
In [345]:
df_who.Region.nunique()
Out[345]:
- If we wanted to filter the DataFrame to only show Region with the 'africa' or 'americas' or 'south-east asia', we could use multiple conditions separated by the "or" operator:
In [346]:
df_who[(df_who.Region == 'Africa') |
(df_who.Region == 'Americas') |
(df_who.Region == 'South-East Asia')]
Out[346]:
isin¶
- However, you can actually rewrite this code more clearly by using the isin() method and passing it a list of Region:
- Return boolean DataFrame showing whether each element in the DataFrame is contained in values.
- More Information
In [347]:
df_who[df_who.Region.isin(['Africa', 'Americas', 'South-East Asia'])]
Out[347]:
- And if you want to reverse this filter, so that you are excluding (rather than including) those three Region, you can put a tilde in front of the condition:
In [348]:
df_who[~df_who.Region.isin(['Africa', 'Americas', 'South-East Asia'])]
Out[348]:
- This works because tilde is the "not" operator in Python.
Filter a DataFrame by largest/smallest categories¶
- Let's say that you needed to filter the WHO DataFrame by Region, but only include the 3 largest Region.
- We'll start by taking the value_counts() of genre and saving it as a Series called counts:
value_counts()¶
In [349]:
from IPython.display import YouTubeVideo
YouTubeVideo('QTVTq8SPzxM',width=900, height=500)
Out[349]:
In [350]:
df_who.Region.value_counts()
Out[350]:
- The Series method nlargest() makes it easy to select the 3 largest values in this Series:
- Return the first n rows ordered by columns in descending order.
- More Information
sort_index¶
In [351]:
from IPython.display import YouTubeVideo
YouTubeVideo('15q-is8P_H4',width=900, height=500)
Out[351]:
Example-1¶
In [352]:
df_who.Region.value_counts().sort_index()
Out[352]:
Example-2¶
In [353]:
df_who.Region.value_counts(normalize=True).sort_index()
Out[353]:
Example-3¶
In [354]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.head()
Out[354]:
In [355]:
df_who.sort_index(axis=1, inplace=True)
In [356]:
df_who.head()
Out[356]:
nlargest()¶
In [357]:
df_who.Region.value_counts().nlargest(n=3)
Out[357]:
- And all we actually need from this Series is the index:
In [358]:
df_who.Region.value_counts().nlargest(n=3).index
Out[358]:
In [359]:
df_who[df_who.Region.isin(df_who.Region.value_counts().nlargest(3).index)]
Out[359]:
In [360]:
df_who[~df_who.Region.isin(df_who.Region.value_counts().nlargest(3).index)]
Out[360]:
- we will use nlargest to select the three rows having the largest values in column “Population”.
In [361]:
df_who.nlargest(3, 'Population')
Out[361]:
- When using keep='last', ties are resolved in reverse order:
In [362]:
df_who.nlargest(3, 'LifeExpectancy', keep='last')
Out[362]:
- When using keep='all', all duplicate items are maintained:
In [363]:
df_who.nlargest(3, 'LifeExpectancy', keep='all')
Out[363]:
nsmallest()¶
- More Information
- Get the rows of a DataFrame sorted by the n smallest values of columns.
In [364]:
df_who.Region.value_counts().nsmallest(3)
Out[364]:
- And all we actually need from this Series is the index:
In [365]:
df_who.Region.value_counts().nsmallest(3).index
Out[365]:
In [366]:
df_who[df_who.Region.isin(df_who.Region.value_counts().nsmallest(3).index)]
Out[366]:
In [367]:
df_who[~df_who.Region.isin(df_who.Region.value_counts().nsmallest(3).index)]
Out[367]:
In [368]:
df_who.nsmallest(3, 'Population')
Out[368]:
between¶
In [369]:
df_who= pd.read_csv('WHO_csv.csv')
In [370]:
df_who[df_who['Population'].between(1, 500)]
Out[370]:
11.19 Arithmetic Operations¶
In [371]:
df = pd.DataFrame({
'a': [4, 5, 6, 7],
'b': [10, 20, 30, 40],
'c': [100, 50, -30, -50]
})
df
Out[371]:
add¶
- More Information
- Addition of dataframe and other, element-wise (binary operator add).
Example-1¶
Method-1¶
In [372]:
df+1
Out[372]:
Method-2¶
In [373]:
df.add(1)
Out[373]:
In [374]:
df + [1, 1,1]
Out[374]:
Method-2¶
In [375]:
df.add([1,1,1])
Out[375]:
Exaple-3¶
In [376]:
df.add([1,2,3], axis='columns')
Out[376]:
Example-4 (add two data farme)¶
In [377]:
df1 = pd.DataFrame({
'a': [4, 5, 6, 7],
'b': [10, 20, 30, 40],
'c': [100, 50, -30, -50]
})
df1
Out[377]:
In [378]:
df2 = pd.DataFrame({
'a': [1, 2, 3, 4],
'b': [5, 6, 7, 8],
'c': [9, 10, 11, 12]
})
df2
Out[378]:
Method-1¶
In [379]:
df1+df2
Out[379]:
Method-2¶
In [380]:
df1.add(df2)
Out[380]:
subtract¶
- Subtraction of dataframe and other, element-wise (binary operator sub).
- More Information1
- More Information2
In [381]:
df = pd.DataFrame({
'a': [4, 5, 6, 7],
'b': [10, 20, 30, 40],
'c': [100, 50, -30, -50]
})
df
Out[381]:
In [382]:
df-1
Out[382]:
Method-2¶
In [383]:
df.sub(1)
Out[383]:
Method-3¶
In [384]:
df.subtract(1)
Out[384]:
In [385]:
df - [1, 1,1]
Out[385]:
Mehtod-2¶
In [386]:
df.sub([1, 1,1])
Out[386]:
Method-3¶
In [387]:
df.subtract([1, 1,1])
Out[387]:
In [388]:
df.sub([1, 2,3], axis='columns')
Out[388]:
Method-2¶
In [389]:
df.subtract([1, 2,3], axis='columns')
Out[389]:
Example-4 (sub two data farme)¶
In [390]:
df1 = pd.DataFrame({
'a': [4, 5, 6, 7],
'b': [10, 20, 30, 40],
'c': [100, 50, -30, -50]
})
df1
Out[390]:
In [391]:
df2 = pd.DataFrame({
'a': [1, 2, 3, 4],
'b': [5, 6, 7, 8],
'c': [9, 10, 11, 12]
})
df2
Out[391]:
Method-1¶
In [392]:
df1-df2
Out[392]:
Method-2¶
In [393]:
df1.sub(df2)
Out[393]:
Method-3¶
In [394]:
df1.subtract(df2)
Out[394]:
shift¶
- Shift index by desired number of periods with an optional time freq
- More Information
Example-1¶
In [395]:
df= pd.read_csv('egg_price.csv')
df
Out[395]:
In [396]:
df= pd.read_csv('egg_price.csv',parse_dates=['Date'],index_col='Date')
df
Out[396]:
In [397]:
#shift the Price of eggs previous day
df['previous_day']=df['Price'].shift(1)
df
Out[397]:
In [398]:
#shift the Price of eggs next day
df['next_day']=df['Price'].shift(-1)
df
Out[398]:
In [399]:
df['1daychange']=df['previous_day']-df['next_day']
df
Out[399]:
In [400]:
df['5days%return']= (df['Price']-df['Price'].shift(5))*100/df['Price'].shift(5)
df
Out[400]:
diff¶
- More Information
- First discrete difference of element.
- Calculates the difference of a DataFrame element compared with another element in the DataFrame (default is the element in the same column of the previous row).
In [401]:
df = pd.DataFrame({'a': [1, 2, 3, 4, 5, 6],
'b': [1, 1, 2, 3, 5, 8],
'c': [1, 4, 9, 16, 25, 36]})
df
Out[401]:
In [402]:
df.diff()
Out[402]:
- Difference with previous column
In [403]:
df.diff(axis=1)
Out[403]:
- Difference with 3rd previous row
In [404]:
df.diff(periods=3)
Out[404]:
- Difference with following row
In [405]:
df.diff(periods=-1)
Out[405]:
multiply¶
- Multiplication of dataframe and other, element-wise (binary operator mul).
- More Information1
- More Information2
In [406]:
df = pd.DataFrame({
'a': [4, 5, 6, 7],
'b': [10, 20, 30, 40],
'c': [100, 50, -30, -50]
})
df
Out[406]:
In [407]:
df*2
Out[407]:
Method-2¶
In [408]:
df.mul(2)
Out[408]:
Method-3¶
In [409]:
df.multiply(2)
Out[409]:
In [410]:
df*[1,2,3]
Out[410]:
Method-2¶
In [411]:
df.mul([1,2,3])
Out[411]:
Method-3¶
In [412]:
df.multiply([1,2,3])
Out[412]:
In [413]:
df = pd.DataFrame({
'a': [4, 5, 6, 7],
'b': [10, 20, 30, 40],
'c': [100, 50, -30, -50]
})
df
Out[413]:
Method-1¶
In [414]:
df.mul([1,2,3],axis='columns')
Out[414]:
Method-2¶
In [415]:
df.multiply([1,2,3],axis='columns')
Out[415]:
Example-4 (Multiply two data farme)¶
In [416]:
df1 = pd.DataFrame({
'a': [4, 5, 6, 7],
'b': [10, 20, 30, 40],
'c': [100, 50, -30, -50]
})
df1
Out[416]:
In [417]:
df2 = pd.DataFrame({
'a': [1, 2, 3, 4],
'b': [5, 6, 7, 8],
'c': [9, 10, 11, 12]
})
df2
Out[417]:
Method-1¶
In [418]:
df1*df2
Out[418]:
Method-2¶
In [419]:
df1.mul(df2)
Out[419]:
Method-3¶
In [420]:
df1.multiply(df2)
Out[420]:
dot¶
- Matrix multiplication with DataFrame or Series objects.
Example-1¶
In [421]:
df1 = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
In [422]:
df1
Out[422]:
In [423]:
df2 = pd.DataFrame({"A":[5, 6],
"B":[7, 8]})
In [424]:
df2
Out[424]:
np.dot(df1, df2)
Example-2¶
In [425]:
df1 = pd.Series([7, 5, 6, 4, 9])
df1
Out[425]:
In [426]:
df2= pd.Series([1, 2, 3, 10, 2])
df2
Out[426]:
(7)(1) + (5)(2) + (6)(3) + (4)(10)+ (9)(2) = 7 + 10 + 18 + 40 + 18 = 93
In [427]:
df1.dot(df2)
Out[427]:
product¶
- Return the product of the values for the requested axis
- More Information1
- More Information2
In [428]:
df = pd.DataFrame({"A":[5, 6],
"B":[7, 8]})
df
Out[428]:
In [429]:
df.prod(axis = 0)
Out[429]:
In [430]:
df.prod(axis = 1)
Out[430]:
Method-2¶
In [431]:
df.product(axis = 0)
Out[431]:
In [432]:
df.product(axis = 1)
Out[432]:
divide¶
- More Information1
- More Information2
- More Information3
- More Information4
- Floating division of dataframe and other, element-wise
Example-1¶
Method-1¶
In [433]:
df = pd.DataFrame({"A":[2, 4],
"B":[6, 8]})
df
Out[433]:
In [434]:
df/2
Out[434]:
Method-2¶
In [435]:
df.div(2)
Out[435]:
Method-3¶
In [436]:
df.divide(2)
Out[436]:
Method-4¶
In [437]:
df.rdiv(2)
#2/df
Out[437]:
Example-2¶
In [438]:
df = pd.DataFrame({"A":[2, 4],
"B":[6, 8]})
df
Out[438]:
Method-1¶
In [439]:
df/[2,4]
Out[439]:
Method-2¶
In [440]:
df.div([2,4])
Out[440]:
Method-3¶
In [441]:
df.divide([2,4])
Out[441]:
Method-4¶
In [442]:
df.rdiv([2,4])
Out[442]:
Example-3¶
In [443]:
df = pd.DataFrame({"A":[2, 4],
"B":[6, 8]})
df
Out[443]:
Method-1¶
In [444]:
df.div([2,4],axis='columns')
Out[444]:
Method-2¶
In [445]:
df.divide([2,4],axis='columns')
Out[445]:
Method-3¶
In [446]:
df.rdiv([2,4],axis='columns')
Out[446]:
Example-4 (div Two Data Frames)¶
In [447]:
df1 = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
df1
Out[447]:
In [448]:
df2 = pd.DataFrame({"A":[5, 6],
"B":[7, 8]})
df2
Out[448]:
Method-1¶
In [449]:
df1/df2
Out[449]:
Method-2¶
In [450]:
df1.div(df2)
Out[450]:
Method-3¶
In [451]:
df1.divide(df2)
Out[451]:
Method-4¶
In [452]:
df1.rdiv(df2)
Out[452]:
floordiv¶
- Integer division of dataframe and other, element-wise (binary operator floordiv).
In [453]:
df = pd.DataFrame({"A":[5, 3, None, 4],
"B":[None, 2, 4, 3],
"C":[4, 3, 8, 5],
"D":[5, 4, 2, None]})
df
Out[453]:
In [454]:
df.floordiv(2, fill_value = 50)
Out[454]:
mod¶
- More Information
- Modulo of dataframe and other, element-wise (binary operator mod).
In [455]:
df= pd.DataFrame({"A":[5, 3, None, 4],
"B":[None, 2, 4, 3],
"C":[4, 3, 7, 5],
"D":[5, 4, 2, None]})
df
Out[455]:
In [456]:
df.mod(3)
Out[456]:
pow¶
- More Information
- Exponential power of dataframe and other, element-wise (binary operator pow).
Example-1¶
In [457]:
df = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
df
Out[457]:
In [458]:
df.pow(2)
Out[458]:
Example-2¶
In [459]:
df1 = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
In [460]:
df1
Out[460]:
In [461]:
df2 = pd.DataFrame({"A":[5, 6],
"B":[7, 8]})
In [462]:
df2
Out[462]:
In [463]:
df1.pow(df2)
Out[463]:
abs¶
- More Information
- Return a Series/DataFrame with absolute numeric value of each element.
- This function only applies to elements that are all numeric.
Example-1¶
In [464]:
df=pd.DataFrame({
'Temperature':[-30,32,-34,36],
'Windspeed':[6,7,10,-12]
})
df
Out[464]:
In [465]:
df['Windspeed'] = df['Windspeed'].abs()
df
Out[465]:
Example-2¶
In [466]:
df=pd.DataFrame({
'Temperature':[-30,32,-34,36,1.2 + 1j],
'Windspeed':[6,7,10,-12,1.2 + 1j]
})
df
Out[466]:
In [467]:
df=df.abs()
df
Out[467]:
round¶
- More Information
- Round a DataFrame to a variable number of decimal places
Example-1¶
In [468]:
df.round(0)
Out[468]:
Example-2¶
In [469]:
df.round({'Temperature': 0, 'Windspeed': 1})
Out[469]:
ceil¶
In [470]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.head()
Out[470]:
In [471]:
df_who['Over60']=df_who['Over60'].apply(np.ceil)
df_who.head()
Out[471]:
floor¶
In [472]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.head()
Out[472]:
In [473]:
df_who['Over60']=df_who['Over60'].apply(np.floor)
df_who.head()
Out[473]:
11.20 logical operations¶
eq¶
- Wrapper for flexible comparison methods eq
- eq() is used to compare every element of Caller series with passed series.
- It returns True for every element which is Equal to the element in passed series.
In [474]:
df1 = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
df1
Out[474]:
In [475]:
df2 = pd.DataFrame({"A":[1, 2],
"B":[3, 8]})
df2
Out[475]:
In [476]:
df1.eq(df2)
Out[476]:
equals¶
- Determines if two NDFrame objects contain the same elements. NaNs in the same location are considered equal.
In [477]:
df1 = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
df1
Out[477]:
In [478]:
df2 = pd.DataFrame({"A":[1, 2],
"B":[3, 8]})
df2
Out[478]:
In [479]:
df1.equals(df2)
Out[479]:
ge¶
- Wrapper for flexible comparison methods ge
- ge() is used to compare every element of Caller series with passed series.
- It returns True for every element which is Greater than or Equal to the element in passed series.
In [480]:
df1 = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
df1
Out[480]:
In [481]:
df2 = pd.DataFrame({"A":[1, 2],
"B":[3, 8]})
In [482]:
df1.ge(df2)
Out[482]:
gt¶
- Wrapper for flexible comparison methods gt
In [483]:
df1 = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
df1
Out[483]:
In [484]:
df2 = pd.DataFrame({"A":[1, 2],
"B":[3, 8]})
df2
Out[484]:
In [485]:
df1.gt(df2)
Out[485]:
le¶
- Wrapper for flexible comparison methods le
In [486]:
df1 = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
df1
Out[486]:
In [487]:
df2 = pd.DataFrame({"A":[1, 2],
"B":[3, 8]})
df2
Out[487]:
In [488]:
df1.le(df2)
Out[488]:
lt¶
- Wrapper for flexible comparison methods lt
In [489]:
df1 = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
df1
Out[489]:
In [490]:
df2 = pd.DataFrame({"A":[1, 2],
"B":[3, 8]})
df2
Out[490]:
In [491]:
df1.lt(df2)
Out[491]:
ne¶
- Wrapper for flexible comparison methods ne
In [492]:
df1 = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
df1
Out[492]:
In [493]:
df2 = pd.DataFrame({"A":[1, 2],
"B":[3, 8]})
df2
Out[493]:
In [494]:
df1.ne(df2)
Out[494]:
all any and bool¶
- All :Return whether all elements are True, potentially over an axis
- Any :Return whether any element is True over requested axis.
- bool: - Return the bool of a single element PandasObject.
- This must be a boolean scalar value, either True or False.
- Raise a ValueError if the PandasObject does not have exactly 1 element, or that element is not boolean.
- More Information1
- More Information2
- More Information3
In [495]:
df = pd.DataFrame()
df['x'] = [1,2,16]
df['y'] = [3,4,5]
In [496]:
df['z']=df['x'] < df['y']
df
Out[496]:
In [497]:
df['z'].all()
Out[497]:
In [498]:
df['z'].any()
Out[498]:
In [499]:
df['z'].bool
Out[499]:
11.21 Miscellaneous Operations¶
In [500]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.head()
Out[500]:
In [501]:
from IPython.display import YouTubeVideo
YouTubeVideo('bofaC0IckHo',width=900, height=500)
Out[501]:
Upper Case¶
In [502]:
df_who['Country']=df_who.Country.str.upper()
df_who.head()
Out[502]:
Lower Case¶
In [503]:
df_who['Region']=df_who.Region.str.lower()
df_who.head()
Out[503]:
capitalize¶
In [504]:
df = pd.Series(['lower', 'CAPITALS', 'this is a sentence', 'SwApCaSe'])
In [505]:
df.str.capitalize()
Out[505]:
title¶
In [506]:
df = pd.Series(['lower', 'CAPITALS', 'this is a sentence', 'SwApCaSe'])
In [507]:
df.str.title()
Out[507]:
swapcase¶
In [508]:
df = pd.Series(['lower', 'CAPITALS', 'this is a sentence', 'SwApCaSe'])
In [509]:
df.str.swapcase()
Out[509]:
Concatenate Two Column in Pandas¶
Method-1¶
In [510]:
df_who['Country_Region']=df_who.Country+'_'+df_who.Region
df_who.head()
Out[510]:
Method-2¶
In [511]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.head()
Out[511]:
In [512]:
df_who['c_r']=df_who['Country'].str.cat(df_who['Region'], sep=',')
df_who.head()
Out[512]:
insert Column in Pandas¶
- More Information
- Insert column into DataFrame at specified location
In [513]:
df_who.insert(2,'Country_Region',df_who.Country+'_'+df_who.Region)
df_who.head()
Out[513]:
In [514]:
df_who.insert(2,'Country_Region',df_who.Country+'_'+df_who.Region,allow_duplicates=True)
df_who.head()
Out[514]:
eval¶
- Evaluate a string describing operations on DataFrame columns.
- Operates on columns only, not specific rows or elements.
- This allows eval to run arbitrary code, which can make you vulnerable to code injection if you pass user input to this function.
- More Information
In [515]:
df = pd.DataFrame({"A":[1, 2],
"B":[3, 4]})
df = df.eval('C=A + B')
df
Out[515]:
assign¶
- Assign new columns to a DataFrame, returning a new object (a copy) with the new columns added to the original ones.
- More Information1
- More Information2
In [516]:
df = pd.DataFrame({
'a': [4, 5, 6, 7],
'b': [10, 20, 30, 40],
'c': [100, 50, -30, -50]
})
df
Out[516]:
In [517]:
df.assign(D=df['a']**2, E=df.b*2)
Out[517]:
query¶
- Query the columns of a frame with a boolean expression.
- More Information
Example-1¶
In [518]:
df = pd.DataFrame()
df['x'] = [11,2,16]
df['y'] = [3,4,15]
In [519]:
df.query('x > y')
Out[519]:
Example-2¶
In [520]:
df1=pd.DataFrame({
'ID':['F2017313014','F2017313015','F2017313017'],
'Marks':[98,97,99],
'Name':['Umer','Ali','Raza']
})
df1
Out[520]:
In [521]:
df2=pd.DataFrame({
'ID':['F2017313014','F2017313018'],
'age':[20,22],
})
df2
Out[521]:
In [522]:
df=pd.merge(df1,df2,on="ID",how="outer",indicator=True)
df
Out[522]:
In [523]:
df=df.query('_merge != "both"')
df
Out[523]:
Drop Columns in Pandas¶
In [524]:
from IPython.display import YouTubeVideo
YouTubeVideo('gnUKkS964WQ',width=900, height=500)
Out[524]:
- More Information
- Drop specified labels from rows or columns
In [525]:
df_who.drop(['Country_Region'],axis=1,inplace=True)
df_who.head()
Out[525]:
In [526]:
df_who.pop('Country')
df_who.head()
Out[526]:
Drop Rows in Pandas¶
In [527]:
df_who= pd.read_csv('WHO_csv.csv')
df_who.drop(df_who.index[[0,2,4]],axis=0,inplace=True)
df_who.head()
Out[527]:
Sort Data Frame¶
In [528]:
from IPython.display import YouTubeVideo
YouTubeVideo('zY4doF6xSxY',width=900, height=500)
Out[528]:
Sort Data Frame w.r.t column¶
- Sort by the values along either axis.
- More Information1
- More Information2
In [529]:
df_who.sort_values('Population')
Out[529]:
In [530]:
df_who.sort_values('Population',ascending=True)
Out[530]:
In [531]:
df_who.sort_values('Population',ascending=False)
Out[531]:
Sort Data Frame w.r.t row¶
In [532]:
df= pd.read_csv('radj.csv',header=0)
df
Out[532]:
In [533]:
pd.DataFrame(np.sort(df.values, axis=1), index=df.index, columns=df.columns)
Out[533]:
Sort by multiple columns¶
In [534]:
df=pd.read_csv("GTRX.txt",skiprows=1,sep=',')
In [535]:
df=df.sort_values(['Cell Name','Is Main BCCH TRX'],ascending=[True,False])
In [536]:
df.to_csv('output8.csv')
argsort¶
In [537]:
df = pd.DataFrame({
'a': [4, 5, 6, 7],
'b': [10, 20, 30, 40],
'c': [100, 50, -30, -50]
})
df
Out[537]:
In [538]:
df.loc[(df.c - 43).abs().argsort()]
Out[538]:
rank¶
- rank method returns a rank of every respective index of a series passed.
- The rank is returned on the basis of position after sorting.
- More Information
In [539]:
df_who["Rank"] = df_who["Population"].rank(ascending=True)
In [540]:
df_who.head()
Out[540]:
In [541]:
df_who.sort_values("Population", inplace = True)
In [542]:
df_who.head()
Out[542]:
Pandas trim leading and trailing white space in a dataframe¶
Example-1¶
In [543]:
df=pd.DataFrame({
'Day':[' 1/1/2019 ',' 1/2/2019 ',' 1/3/2019',' 1/4/2019'],
'Temperature':[30,32,34,36],
'Windspeed':[6,7,10,12],
'Event':[' Rain ',' Su nny ','Rain',' Sunny']
})
df
Out[543]:
In [544]:
df[(df.Event=='Rain')]
Out[544]:
In [545]:
df[(df.Day=='1/3/2019')]
Out[545]:
In [546]:
df =df.applymap(lambda x: x.strip() if isinstance(x, str) else x)
df
Out[546]:
In [547]:
df[(df.Event=='Rain')]
Out[547]:
In [548]:
df[(df.Day=='1/3/2019')]
Out[548]:
Example-2¶
In [549]:
df=' hello world!'
df
Out[549]:
In [550]:
df.lstrip()
Out[550]:
lambda¶
In [551]:
df=pd.read_csv("GTRX.txt",skiprows=1)
In [552]:
def band(Frequency):
if Frequency<100:
return 'GSM'
else:
return 'DCS'
In [553]:
df['Band']=df['Frequency'].apply(lambda x:band(x))
In [554]:
df.head()
Out[554]:
where¶
- More Information1
- More Information2
- Return an object of same shape as self and whose corresponding entries are from self where cond is True and otherwise are from other.
Example-1¶
In [555]:
df=pd.read_csv("GTRX.txt",skiprows=1)
In [556]:
df['Band'] = np.where(
((df['Frequency']>=25) & (df['Frequency']<=62)),
'GSM',
np.where(
(df['Frequency']>=556) & (df['Frequency']<=585),
'DCS',
'Other Band'))
In [557]:
df.head()
Out[557]:
Example-2¶
In [558]:
df=pd.read_csv("GTRX.txt",skiprows=1,sep=',')
In [559]:
df['CI'] = np.where(
(df['Cell Name'].str.startswith ('CI-')),
df['Cell Name'].str[8]+""+df['Cell Name'].str[3:7],
np.where(
(df['Cell Name'].str.startswith ('CII-')),
df['Cell Name'].str[9]+""+df['Cell Name'].str[4:8],
np.where(
(df['Cell Name'].str.startswith ('S-')),
df['Cell Name'].str[7]+""+df['Cell Name'].str[2:5],
np.where(
(df['Cell Name'].str.startswith ('N-')),
df['Cell Name'].str[7]+""+df['Cell Name'].str[2:5],
df['Cell Name'].str[:5]))))
In [560]:
df.head()
Out[560]:
Example-3¶
In [561]:
df_who= pd.read_csv('WHO_csv.csv')
In [562]:
df_who.Region.where(df_who.Region.isin(df_who.Region.value_counts().nlargest(3).index),other='Other').value_counts()
Out[562]:
Split a string into multiple columns¶
Method-1¶
In [563]:
spl= pd.read_csv('Scopus_1926-1950.csv',encoding='latin-1')
# Preprocessing
# Remove Space between Author(s)ID
spl['Author(s)ID'] = spl['Author(s)ID'].str.replace(" ","")
# Remove Author(s)ID end with ;
spl['Author(s)ID'] = spl['Author(s)ID'].str.rstrip(';')
- What if we wanted to split the "Author(s)ID" column into Five separate columns, for A0, A1, A2, A3, A4. We would use the str.split() method and tell it to split on a space character and expand the results into a DataFrame
In [564]:
a=spl['Author(s)ID'].str.split(';', expand=True).add_prefix('A')
In [565]:
spl=pd.concat([spl, a], axis=1)
spl.head()
Out[565]:
Method-2¶
In [566]:
spl= pd.read_csv('Scopus_1926-1950.csv',encoding='latin-1')
# Preprocessing
# Remove Space between Author(s)ID
spl['Author(s)ID'] = spl['Author(s)ID'].str.replace(" ","")
# Remove Author(s)ID end with ;
spl['Author(s)ID'] = spl['Author(s)ID'].str.rstrip(';')
- These Five columns can actually be saved to the original DataFrame in a single assignment statement:
In [567]:
spl[['Author0', 'Author1', 'Author2','Author3','Author4']] = spl['Author(s)ID'].str.split(';', expand=True)
In [568]:
spl.head()
Out[568]:
Method-3¶
In [569]:
spl2= pd.read_csv('Scopus_1926-1950.csv',encoding='latin-1')
# Preprocessing
# Remove Space between Author(s)ID
spl2['Author(s)ID'] = spl2['Author(s)ID'].str.replace(" ","")
# Remove Author(s)ID end with ;
spl2['Author(s)ID'] = spl2['Author(s)ID'].str.rstrip(';')
In [570]:
spl2 = spl2.join(spl2['Author(s)ID'].str.split(';', expand=True).add_prefix('Author_'))
In [571]:
spl2.head()
Out[571]:
Special Case¶
- If we only cared about saving the 1st Author ID in column, we can just select that column and save it to the DataFrame:
In [572]:
spl1= pd.read_csv('Scopus_1926-1950.csv',encoding='latin-1')
# Preprocessing
# Remove Space between Author(s)ID
spl1['Author(s)ID'] = spl1['Author(s)ID'].str.replace(" ","")
# Remove Author(s)ID end with ;
spl1['Author(s)ID'] = spl1['Author(s)ID'].str.rstrip(';')
In [573]:
spl1['FAuthor'] = spl1['Author(s)ID'].str.split(';', expand=True)[0]
spl1.head()
Out[573]:
11.22 8 ways to apply LEFT RIGHT MID in Pandas¶
- At times, you may need to extract specific characters within a string.
- You may then apply the concepts of Left, Right, and Mid in pandas to obtain your desired characters within a string.
- the following 8 scenarios to explain how to extract specific characters:
1- From the left¶
In [574]:
Data = pd.DataFrame({'Identifier': ['55555-abc','77777-xyz','99999-mmm']})
Left = Data['Identifier'].str[:5]
Left
Out[574]:
2- From the right¶
In [575]:
Data = pd.DataFrame({'Identifier': ['55555-abc','77777-xyz','99999-mmm']})
Right = Data['Identifier'].str[-3:]
Right
Out[575]:
3- From the middle¶
In [576]:
Data = pd.DataFrame({'Identifier': ['ID-55555-End','ID-77777-End','ID-99999-End']})
Mid = Data['Identifier'].str[3:8]
Mid
Out[576]:
4- Before a symbol¶
In [577]:
Data = pd.DataFrame({'Identifier': ['Umer-55555-H','Ali-77777-A','Ahmed-99999-S']})
BeforeSymbol = Data['Identifier'].str.split('-').str[0]
BeforeSymbol
Out[577]:
5- Before space¶
In [578]:
Data = pd.DataFrame({'Identifier': ['111 IDAA','2222222 IDB','33 IDCCC']})
BeforeSpace = Data['Identifier'].str.split(' ').str[0]
BeforeSpace
Out[578]:
6- After a symbol¶
In [579]:
Data = pd.DataFrame({'Identifier': ['IDAA-111','IDB-2222222','IDCCC-33']})
AfterSymbol = Data['Identifier'].str.split('-').str[1]
AfterSymbol
Out[579]:
7- Between identical symbols¶
In [580]:
Data = pd.DataFrame({'Identifier': ['IDAA-111-AA','IDB-2222222-B','IDCCC-33-CCC']})
BetweenTwoSymbols = Data['Identifier'].str.split('-').str[1]
BetweenTwoSymbols
Out[580]:
8- Between different symbols¶
In [581]:
Data = pd.DataFrame({'Identifier': ['IDAA-111$AA','IDB-2222222$B','IDCCC-33$CCC']})
betweenTwoDifferentSymbols = Data['Identifier'].str.split('-').str[1]
betweenTwoDifferentSymbols = betweenTwoDifferentSymbols.str.split('$').str[0]
betweenTwoDifferentSymbols
Out[581]:
11.23 Missing data handling¶
- Process of cleaning messy data is called data munging or data wranling.
- Fill NaN with specific value
- Fill NaN with Customized Solutions
- Fill NaN with fwd fill method
- Fill NaN with fwd fill method and set Limit
- Fill NaN with backwd method
- Fill NaN with backwd fill method and set Limit
- Fill NaN with fwd fill method on columns
- Fill NaN with fwd fill method on columns with limit
- Fill NaN with bckwd fill method on columns
- Fill NaN with bckwd fill method on columns with limit - Fill NaN using interpolate function
- Drop NaN rows orcolumns
- Drop NaN rows or columns if each element of the row having NaN
- Drop NaN Rows By Appling thresh
In [582]:
from IPython.display import YouTubeVideo
YouTubeVideo('4R4WsDJ-KV',width=900, height=500)
Out[582]:
Example-1¶
In [583]:
df= pd.read_csv("Missing_Values_Handling.csv")
df
Out[583]:
In [584]:
from IPython.display import YouTubeVideo
YouTubeVideo('EaGbS7eWSs0',width=900, height=500)
Out[584]:
Fill NaN with specific value¶
In [585]:
df.fillna(value=0)
Out[585]:
Fill NaN with Customized Solutions¶
In [586]:
df.fillna({
'Temperature':0,
'Windspeed':0,
'Event':'No Event'
})
Out[586]:
Fill NaN with fwd fill method¶
In [587]:
df.fillna(method="ffill")
Out[587]:
Fill NaN with fwd fill method and set Limit¶
In [588]:
df.fillna(method="ffill",limit=1)
Out[588]:
Fill NaN with backwd method¶
In [589]:
df.fillna(method="bfill")
Out[589]:
Fill NaN with backwd fill method and set Limit¶
In [590]:
df.fillna(method="bfill",limit=1)
Out[590]:
Fill NaN with fwd fill method on columns¶
In [591]:
df.fillna(method="ffill",axis="columns")
Out[591]:
Fill NaN with fwd fill method on columns with limit¶
In [592]:
df.fillna(method="ffill",axis="columns",limit=1)
Out[592]:
Fill NaN with bckwd fill method on columns¶
In [593]:
df.fillna(method="bfill",axis="columns")
Out[593]:
Fill NaN with bckwd fill method on columns with limit¶
In [594]:
df.fillna(method="bfill",axis="columns",limit=1)
Out[594]:
Fill NaN using interpolate function¶
In [595]:
df.interpolate()
# This is linear interpolate method
Out[595]:
Drop NaN rows or columns¶
- More Information
- Remove missing values.
In [596]:
df
Out[596]:
In [597]:
df.dropna()
Out[597]:
In [598]:
df.dropna(axis=0)
Out[598]:
In [599]:
df.dropna(axis=1)
Out[599]:
Drop NaN rows or columns if each element of the row having NaN¶
In [600]:
df.set_index('Day',inplace=True)
df.dropna(axis=0,how='all')
Out[600]:
Drop NaN Rows By Appling thresh¶
Example-1¶
In [601]:
df.dropna(thresh=2,axis=0)
Out[601]:
Example-2¶
In [602]:
df.dropna(thresh=len(df)*0.2, axis=0)
Out[602]:
11.24 Replace values methods¶
In [603]:
from IPython.display import YouTubeVideo
YouTubeVideo('XOxABiMhG2U',width=900, height=500)
Out[603]:
In [604]:
df= pd.read_csv("Weather2.csv")
df
Out[604]:
In [605]:
df= pd.read_csv('Weather2.csv',na_values=['Not Available',"n.a"])
df
Out[605]:
Solution-2¶
In [606]:
df= pd.read_csv("Stocks.csv")
df
Out[606]:
In [607]:
df= pd.read_csv("Stocks.csv",na_values=['not available',"n.a","n.a."])
df
Out[607]:
- As we know that Revenue never -ve number
In [608]:
df= pd.read_csv('Stocks.csv',na_values={'EPS':["not available","n.a","n.a."],
'Revenue':["not available","n.a","n.a.",-1],
'Price':["not available","n.a","n.a."],
'People':["not available","n.a","n.a."]
})
df
Out[608]:
In [609]:
df=pd.read_excel("Stocks_ex.xlsx","Stocks")
df
Out[609]:
Example-1¶
In [610]:
def convert_people_cell(cell):
if cell=="n.a.":
return 'Sam Walton'
return cell
In [611]:
df=pd.read_excel("Stocks_ex.xlsx","Stocks",converters={
'People':convert_people_cell
})
df
Out[611]:
Example-2¶
In [612]:
def convert_people_cell(cell):
if cell=="n.a.":
return 'Sam Walton'
return cell
def convert_revenue_cell(cell):
if cell<0:
return 'NaN'
return cell
In [613]:
df=pd.read_excel("Stocks_ex.xlsx","Stocks",converters={
'People':convert_people_cell,
'Revenue':convert_revenue_cell
})
df
Out[613]:
Solution-4¶
Replace¶
Example-1¶
In [614]:
df= pd.read_csv('Replace_DS.csv')
df
Out[614]:
In [615]:
df.replace(-999999,np.NaN)
Out[615]:
Example-2¶
In [616]:
df.replace({
-999999:np.NaN,
'No Event':np.NaN})
Out[616]:
Example-3¶
In [617]:
df= pd.read_csv('Replace_DS1.csv')
df
Out[617]:
In [618]:
df.replace([-999999,-888888,'No Event'],np.NaN)
Out[618]:
Example-4¶
In [619]:
df= pd.read_csv('Replace_DS2.csv')
df
Out[619]:
In [620]:
df.replace({
'Temperature':-999999,
'Windspeed':-888888,
'Event':'0'},np.NaN)
Out[620]:
Example-5¶
In [621]:
df= pd.read_csv('Replace_DS3.csv')
df
Out[621]:
In [622]:
df.replace('[A-Za-z]','',regex=True)
Out[622]:
- Above statment works for Temperature and Windspeed, but not required on Day and Event
In [623]:
df.replace({
'Temperature':'[A-Za-z]',
'Windspeed':'[A-Za-z]'},'',regex=True)
Out[623]:
mask¶
- More Information
- Return an object of same shape as self and whose corresponding entries are from self where cond is False and otherwise are from other.
Example-1¶
In [624]:
df = pd.DataFrame({"A":[12, 4, 5, 44, 1],
"B":[5, 2, 54, 3, 2],
"C":[20, 16, 7, 3, 8],
"D":[14, 3, 17, 2, 6]})
df
Out[624]:
In [625]:
df.mask(df > 10, -25)
Out[625]:
Example-2¶
In [626]:
df = pd.DataFrame({"A":[12, 4, 5, None, 1],
"B":[7, 2, 54, 3, None],
"C":[20, 16, 11, 3, 8],
"D":[14, 3, None, 2, 6]})
df
Out[626]:
In [627]:
df.mask(df.isna(), 1000)
Out[627]:
clip_lower¶
- (DEPRECATED) Return copy of the input with values below a threshold truncated.
- More Information
In [628]:
df=pd.DataFrame({'col_0': [9, -3, 0, -1, 5], 'col_1': [-2, -7, 6, 8, -5]})
df
Out[628]:
In [629]:
df.clip_lower(3)
Out[629]:
clip_upper¶
- (DEPRECATED) Return copy of input with values above given value(s) truncated
- More Information
In [630]:
df=pd.DataFrame({'col_0': [9, -3, 0, -1, 5], 'col_1': [-2, -7, 6, 8, -5]})
df
Out[630]:
In [631]:
df.clip_upper(3)
Out[631]:
clip¶
- Trim values at input threshold(s).
- Assigns values outside boundary to boundary values.
- Thresholds can be singular values or array like, and in the latter case the clipping is performed element-wise in the specified axis.
- More Information
In [632]:
df=pd.DataFrame({'col_0': [9, -3, 0, -1, 5], 'col_1': [-2, -7, 6, 8, -5]})
df
Out[632]:
In [633]:
df.clip(-4, 6)
Out[633]:
combine¶
- Pandas Series.combine() is a series mathematical operation method.
- This is used to combine two series into one. The shape of output series is same as the caller series.
- The elements are decided by a function passed as parameter to combine() method.
- The shape of both series has to be same otherwise it will throw an error
- More Information
Exapmple-1¶
In [634]:
df1 = pd.DataFrame({'A': [0, 2], 'B': [4, 4]})
df1
Out[634]:
In [635]:
df2 = pd.DataFrame({'A': [0, 1], 'B': [8, 8]})
df2
Out[635]:
In [636]:
df1.combine(df2, lambda s1, s2: s1 if s1.sum() < s2.sum() else s2)
Out[636]:
Example-2¶
In [637]:
df1 =pd.Series([1, 2, 5, 6, 3, 7, 11, 0, 4])
df1
Out[637]:
In [638]:
df2 =pd.Series([5, 3, 2, 1, 3, 9, 21, 3, 1])
df2
Out[638]:
In [639]:
df1.combine(df2, (lambda x1, x2: x1 if x1 < x2 else x2))
Out[639]:
combine_first¶
- Two series are created from list using Pandas Series() method.
- Some Null values are also passed to each list using Numpy np.nan.
- Both series are then combined using .combine_first() method.
- At first, the method is called by series1 and result is stored in result1 and then similarly it is called by series2 and stored in result2.
- Both of the returned series are then printed to compare outputs.
- More Information
In [640]:
series1 = pd.Series([70, 5, 0, 225, 1, 16, np.nan, 10, np.nan])
series2 = pd.Series([27, np.nan, 2, 23, 1, 95, 53, 10, 5])
series3 = series1.combine_first(series2)
series3
Out[640]:
In [641]:
series4 = series2.combine_first(series1)
series4
Out[641]:
update¶
- Modify in place using non-NA values from another DataFrame
- More Information
In [642]:
df= pd.DataFrame({'A': ['a', 'b', 'c'],
'B': ['x', 'y', 'z']})
df
Out[642]:
In [643]:
df1 = pd.DataFrame({'B': ['d', 'e', 'f', 'g', 'h', 'i']})
df1
Out[643]:
In [644]:
df.update(df1)
In [645]:
df
Out[645]:
11.25 Varibale types handling¶
Categorical Data¶
In [646]:
df= pd.read_csv('List.csv')
df
Out[646]:
In [647]:
df.sort_values("Score")
Out[647]:
In [648]:
from pandas.api.types import CategoricalDtype
df['Score'] = df['Score'].astype(CategoricalDtype(categories=['Poor','Good','V Good','Exceptional'],ordered=True))
df
Out[648]:
In [649]:
df.sort_values("Score")
Out[649]:
In [650]:
df.loc[df.Score >= 'Good']
Out[650]:
get_dummies¶
- If we wanted to separate the distinct variables out into booleans as we would like for data science models such as for example, linear regression, we can use pd.get_dummies.
In [651]:
from IPython.display import YouTubeVideo
YouTubeVideo('0s_1IsROgDc',width=900, height=500)
Out[651]:
In [652]:
pd.get_dummies(df, columns=['Score'])
Out[652]:
Nominal Categories¶
In [653]:
df= pd.read_csv('Gender.csv')
df
Out[653]:
In [654]:
df['Sex']=df.Gender.astype("category").cat.codes
df
Out[654]:
map¶
In [655]:
df= pd.read_csv('Gender.csv')
df
Out[655]:
In [656]:
df['Sex_num'] = df.Gender.map({'Female':0, 'Male':1})
df
Out[656]:
factorize¶
In [657]:
df_who= pd.read_csv('WHO_csv.csv')
In [658]:
df_who['Reg_code']=df_who.Region.factorize()[0]
In [659]:
df_who.to_csv('test.csv')
Example¶
In [660]:
df_who= pd.read_csv('WHO_csv.csv')
In [661]:
df_who.memory_usage()
Out[661]:
In [662]:
df_who= pd.read_csv('WHO_csv.csv',dtype = {"Region" : "category"})
In [663]:
df_who.dtypes
Out[663]:
In [664]:
df_who.memory_usage()
Out[664]:
Example¶
In [665]:
df_who= pd.read_csv('WHO_csv.csv',dtype = {"Region" : "category",'Country':'category'})
In [666]:
df_who.dtypes
Out[666]:
In [667]:
df_who.memory_usage()
Out[667]:
Convert continuous data into categorical data¶
In [668]:
df= pd.read_csv('titanic.csv')
df.head()
Out[668]:
- It's currently continuous data, but what if you wanted to convert it into categorical data.
- One solution would be to label the age ranges, such as "child", "young adult", and "adult". The best way to do this is by using the cut() function:
In [669]:
df['cage']=pd.cut(df.Age, bins=[0, 18, 25, 99], labels=['child', 'young adult', 'adult'])
In [670]:
df.head()
Out[670]:
In [671]:
df.tail()
Out[671]:
In [672]:
df['cage'].dtypes
Out[672]:
- This assigned each value to a bin with a label.
- Ages 0 to 18 were assigned the label "child",
- ages 18 to 25 were assigned the label "young adult",
- and ages 25 to 99 were assigned the label "adult".
- Notice that the data type is now "category", and the categories are automatically ordered.
qcut¶
- More Information
- Quantile-based discretization function.
- Discretize variable into equal-sized buckets based on rank or based on sample quantiles.
- For example 1000 values for 10 quantiles would produce a Categorical object indicating quantile membership for each data point.
Example-1¶
In [673]:
df= pd.read_csv('titanic.csv')
In [674]:
df['cage']=pd.qcut(df.Age,q=3)
In [675]:
df
Out[675]:
Examplw-2¶
In [676]:
df= pd.read_csv('titanic.csv')
In [677]:
df['cage']=pd.qcut(df.Age,q=3, labels=['child', 'young adult', 'adult'])
In [678]:
df
Out[678]:
Example-3¶
In [679]:
import pandas as pd
df= pd.qcut(range(5), 4)
df
Out[679]:
In [680]:
df=pd.qcut(range(5), 3, labels=["good", "medium", "bad"])
df
Out[680]:
In [681]:
df=pd.qcut(range(5), 4, labels=False)
df
Out[681]:
11.26 CONCATENATE¶
In [682]:
from IPython.display import YouTubeVideo
YouTubeVideo('WGOEFok1szA',width=900, height=500)
Out[682]:
CONCATENATE w.r.t. rows¶
In [683]:
df1= pd.read_csv('KPK_Weather.csv')
df1
Out[683]:
In [684]:
df2= pd.read_csv('Punjab_Weather.csv')
df2
Out[684]:
In [685]:
df=pd.concat([df1,df2])
df
Out[685]:
Solution for Index¶
In [686]:
df=pd.concat([df1,df2],ignore_index=True)
df
Out[686]:
keys¶
In [687]:
df=pd.concat([df1,df2],keys=['KPK','Punjab'])
df
Out[687]:
In [688]:
df.loc['Punjab']
Out[688]:
In [689]:
df.loc['KPK']
Out[689]:
CONCATENATE w.r.t col¶
Exammple-1¶
In [690]:
df1= pd.read_csv('WTemData.csv')
df1
Out[690]:
In [691]:
df2= pd.read_csv('WWSData.csv')
df2
Out[691]:
In [692]:
df=pd.concat([df1,df2])
df
Out[692]:
Solution¶
In [693]:
df=pd.concat([df1,df2],axis=1)
df
Out[693]:
Example-2¶
In [694]:
df1=pd.DataFrame({
'City':['LHR','SHD','GJW','SKT'],
'temperature':[30,32,34,36]})
df1
Out[694]:
In [695]:
df2=pd.DataFrame({
'City':['SKT','GJW','SHD','LHR'],
'windspeed':[7,8,9,10]})
df2
Out[695]:
In [696]:
df=pd.concat([df1,df2],axis=1)
df
Out[696]:
Solution¶
In [697]:
df1=pd.DataFrame({
'City':['LHR','SHD','GJW','SKT'],
'temperature':[30,32,34,36]},index=[0,1,2,3])
df1
Out[697]:
In [698]:
df2=pd.DataFrame({
'City':['SKT','GJW','SHD','LHR'],
'windspeed':[7,8,9,10]},index=[3,2,1,0])
df2
Out[698]:
In [699]:
df=pd.concat([df1,df2],axis=1)
df
Out[699]:
11.27 Merge Dataframes¶
In [700]:
from IPython.display import YouTubeVideo
YouTubeVideo('h4hOPGo4UVU',width=900, height=500)
Out[700]:
Inner Join¶
- Join columns with other DataFrame either on index or on a key column.
- Efficiently Join multiple DataFrame objects by index at once by passing a list.
- More Information1
- More Information2
Example-1¶
In [701]:
df1=pd.DataFrame({
'City':['LHR','SHD','GJW','SKT'],
'temperature':[30,32,34,36]})
df1
Out[701]:
In [702]:
df2=pd.DataFrame({
'City':['SKT','GJW','SHD','LHR'],
'windspeed':[7,8,9,10]})
df2
Out[702]:
In [703]:
df=pd.merge(df1,df2,on="City")
df
Out[703]:
Example-2¶
In [704]:
df1= pd.read_csv('Family.csv',skipfooter=1,engine='python')
df1
Out[704]:
In [705]:
df2= pd.read_csv('Math.txt',sep=",")
df2
Out[705]:
In [706]:
df=pd.merge(df1,df2,on="ID")
df
Out[706]:
Example-3¶
In [707]:
df1= pd.read_csv('ListStu.txt',sep=",")
df1
Out[707]:
In [708]:
df2= pd.read_csv('Listage.txt',sep=",")
df2
Out[708]:
In [709]:
df=pd.merge(df1,df2,on="ID")
df
Out[709]:
Example-4¶
In [710]:
df=pd.merge(df1,df2,on="ID",how="inner")
df
Out[710]:
Outer Join¶
In [711]:
df1= pd.read_csv('ListStu.txt',sep=",")
df1
Out[711]:
In [712]:
df2= pd.read_csv('Listage.txt',sep=",")
df2
Out[712]:
In [713]:
df=pd.merge(df1,df2,on="ID",how="outer")
df
Out[713]:
Left Join¶
In [714]:
df1= pd.read_csv('ListStu.txt',sep=",")
df1
Out[714]:
In [715]:
df2= pd.read_csv('Listage.txt',sep=",")
df2
Out[715]:
In [716]:
df=pd.merge(df1,df2,on="ID",how="left")
df
Out[716]:
Right Join¶
In [ ]:
In [717]:
df1= pd.read_csv('ListStu.txt',sep=",")
df1
Out[717]:
In [718]:
df2= pd.read_csv('Listage.txt',sep=",")
df2
Out[718]:
In [719]:
df=pd.merge(df1,df2,on="ID",how="right")
df
Out[719]:
Indicator¶
In [720]:
import pandas as pd
df1= pd.read_csv('ListStu.txt',sep=",")
df1
Out[720]:
In [721]:
df2= pd.read_csv('Listage.txt',sep=",")
df2
Out[721]:
In [722]:
df=pd.merge(df1,df2,on="ID",how="outer",indicator=True)
df
Out[722]:
Suffixes¶
Example-1¶
In [723]:
df1=pd.DataFrame({
'ID':['F2017313014','F2017313015'],
'Marks':[98,97]
})
df1
Out[723]:
In [724]:
df2=pd.DataFrame({
'ID':['F2017313014','F2017313015'],
'Marks':[96,95]
})
df2
Out[724]:
In [725]:
df=pd.merge(df1,df2,on="ID")
df
Out[725]:
Exaample-2¶
In [726]:
df1=pd.DataFrame({
'ID':['F2017313014','F2017313015'],
'Marks':[98,97]
})
df1
Out[726]:
In [727]:
df2=pd.DataFrame({
'ID':['F2017313014','F2017313015'],
'Marks':[96,95]
})
df2
Out[727]:
In [728]:
df=pd.merge(df1,df2,on="ID",suffixes=('_Math','_Physics'))
df
Out[728]:
Example-3¶
In [729]:
df1=pd.DataFrame({
'ID':['F2017313014','F2017313015'],
'Marks':[98,97],
'Name':['Umer','Ali']
})
df1
Out[729]:
In [730]:
df2=pd.DataFrame({
'ID':['F2017313014','F2017313015'],
'Marks':[96,95],
'Name':['Amir','Yaqoob']
})
df2
Out[730]:
In [731]:
df=pd.merge(df1,df2,on="ID",suffixes=('_left','_right'))
df
Out[731]:
merge_ordered¶
- Perform merge with optional filling/interpolation designed for ordered data like time series data.
- Optionally perform group-wise merge (see examples)
In [732]:
df1 = pd.DataFrame({'key': list('aceace'), 'group': list('aaabbb'), 'lvalue': [1, 2, 3, 1, 2, 3]})[[ 'group','key', 'lvalue']]
df1
Out[732]:
In [733]:
df2 = pd.DataFrame({'key': list('bcd'), 'rvalue': [1, 2, 3]})[[ 'rvalue','key']]
df2
Out[733]:
In [734]:
pd.merge_ordered(df1, df2, left_by='group')
Out[734]:
In [735]:
pd.merge_ordered(df1, df2, left_by='group',fill_method='ffill')
Out[735]:
merge_asof¶
- Perform an asof merge. This is similar to a left-join except that we match on nearest key rather than equal keys.
- Both DataFrames must be sorted by the key
- More Information
In [736]:
df1 = pd.DataFrame({'a': [1, 5, 10], 'left_val': ['a', 'b', 'c']})
df1
Out[736]:
In [737]:
df2 = pd.DataFrame({'a': [1, 2, 3, 6, 7],
'right_val': [1, 2, 3, 6, 7]})
df2
Out[737]:
In [738]:
pd.merge_asof(df1, df2, on='a')
Out[738]:
In [739]:
pd.merge_asof(df1, df2, on='a', allow_exact_matches=True)
Out[739]:
In [740]:
pd.merge_asof(df1, df2, on='a', allow_exact_matches=False)
Out[740]:
lookup¶
In [741]:
df1 = pd.DataFrame([[1,2,3,4], [6,7,8,9]], columns=['D', 'B', 'E', 'A'])
df1
Out[741]:
In [742]:
df2 = pd.DataFrame([[10,20,30,40], [60,70,80,90], [600,700,800,900]], columns=['A', 'B', 'C', 'D'])
df2
Out[742]:
In [743]:
a1,a2=df1.align(df2, join='inner', axis=1)
In [744]:
a1
Out[744]:
In [745]:
a2
Out[745]:
append¶
- More Information
- Append rows of other to the end of this frame, returning a new object.
In [746]:
a1.append(a2)
Out[746]:
In [747]:
a1.append(a2,ignore_index=True)
Out[747]:
Add column to pandas dataframe with partial file name while importing many files¶
In [748]:
import os
#import glob
from glob import glob
#all_files = glob.glob('Scopus*.csv')
all_files = glob('Scopus*.csv')
df_from_each_file = (pd.read_csv(f,encoding='latin-1').assign(fname=os.path.basename(f).split('.')[0]) for f in all_files)
concatdf = pd.concat(df_from_each_file, ignore_index=True)
concatdf[['Title','fname']].head()
Out[748]:
Build a DataFrame from multiple files¶
w.r.t rows¶
In [749]:
scopus_files = sorted(glob('Scopus*.csv'))
scopus_files
Out[749]:
- glob returns filenames in an arbitrary order, which is why we sorted the list using Python's built-in sorted() function.
- We can then use a generator expression to read each of the files using read_csv() and pass the results to the concat() function, which will concatenate the rows into a single DataFrame:
In [750]:
pd.concat((pd.read_csv(file,encoding='latin-1') for file in scopus_files))
Out[750]:
- Unfortunately, there are now duplicate values in the index. To avoid that, we can tell the concat() function to ignore the index and instead use the default integer index:
In [751]:
pd.concat((pd.read_csv(file,encoding='latin-1') for file in scopus_files), ignore_index=True)
Out[751]:
w.r.t column¶
In [752]:
student_files = sorted(glob('Student*.csv'))
student_files
Out[752]:
In [753]:
pd.concat((pd.read_csv(file) for file in student_files), axis='columns')
Out[753]:
11.28 Pivot¶
In [754]:
from IPython.display import YouTubeVideo
YouTubeVideo('xPPs59pn6qU',width=900, height=500)
Out[754]:
- More Information
- pivot allows us to transform or reshape data
In [755]:
df= pd.read_csv('Weather3.csv')
df
Out[755]:
In [756]:
df.pivot(index="Date",columns="City")
Out[756]:
- if only Temperature values required.
In [757]:
df.pivot(index="Date",columns="City",values='Temperature')
Out[757]:
11.29 Pivot Table¶
- More Information
- Pivot table is used to summarize and aggregate data inside dataframe
In [758]:
df= pd.read_csv('Weather4.csv')
df
Out[758]:
Example-1¶
In [759]:
df.pivot_table(index="City",columns="Date")
Out[759]:
Example-2¶
In [760]:
df.pivot_table(index="City",columns="Date",values='Temperature')
Out[760]:
Example-3¶
In [761]:
df.pivot_table(index="City",columns="Date",values='Temperature',aggfunc='sum')
Out[761]:
Example-4¶
In [762]:
df.pivot_table(index="City",columns="Date",values='Temperature',aggfunc='count')
Out[762]:
Example-5¶
In [763]:
df.pivot_table(index="City",columns="Date",values='Temperature',aggfunc='mean')
Out[763]:
Example-6¶
In [764]:
df.pivot_table(index="City",columns="Date",values='Temperature',margins=True,aggfunc='sum')
Out[764]:
Example-7¶
In [765]:
df.pivot_table(index="City",columns="Date",values='Temperature',margins=True)
Out[765]:
11.30 Grouper¶
Example-1¶
In [766]:
df= pd.read_csv('Weather5.csv',parse_dates=['Date'])
df
Out[766]:
In [767]:
df.pivot_table(index=pd.Grouper(freq='M',key='Date'),columns='City')
Out[767]:
Example-2¶
In [768]:
df.pivot_table(index=pd.Grouper(freq='M',key='Date'),columns='City',aggfunc='sum')
Out[768]:
11.31 Melt¶
- Melt is used to transform or reshape data
- “Unpivots” a DataFrame from wide format to long format, optionally leaving identifier variables set.
- This function is useful to massage a DataFrame into a format where one or more columns are identifier variables (id_vars), while all other columns, considered measured variables (value_vars), are “unpivoted” to the row axis, leaving just two non-identifier columns, ‘variable’ and ‘value’.
- More Information
In [769]:
from IPython.display import YouTubeVideo
YouTubeVideo('oY62o-tBHF4',width=900, height=500)
Out[769]:
In [770]:
df= pd.read_csv('Weather6.csv')
df
Out[770]:
Example-1¶
In [771]:
df1=pd.melt(df,id_vars=["Day"])
df1
Out[771]:
Example-2¶
In [772]:
df1=pd.melt(df,id_vars=["Day"],var_name="City", value_name='Temp')
df1
Out[772]:
Example-3¶
In [773]:
df1[df1["City"]=="Lahore"]
Out[773]:
11.32 Crosstab¶
In [774]:
from IPython.display import YouTubeVideo
YouTubeVideo('I_kUj-MfYys',width=900, height=500)
Out[774]:
In [775]:
df=pd.read_csv("Handedness.csv")
df
Out[775]:
Example-1¶
In [776]:
pd.crosstab(df.Nationality,df.Handedness)
Out[776]:
Example-2¶
In [777]:
pd.crosstab(df.Sex,df.Handedness)
Out[777]:
Example-3¶
In [778]:
pd.crosstab(df.Sex,df.Handedness,margins=True)
Out[778]:
Example-4¶
In [779]:
pd.crosstab(df.Sex,df.Handedness,margins=True,values=df.Age,aggfunc='mean')
Out[779]:
Example-5¶
In [780]:
pd.crosstab(df.Sex,df.Handedness,normalize='index')
Out[780]:
Example-6¶
In [781]:
pd.crosstab(df.Sex,[df.Handedness,df.Nationality],margins=True)
Out[781]:
Example-7¶
In [782]:
pd.crosstab([df.Sex,df.Nationality],df.Handedness,margins=True)
Out[782]:
11.33 groupby¶
In [783]:
from IPython.display import YouTubeVideo
YouTubeVideo('qy0fDqoMJx8',width=900, height=500)
Out[783]:
- More Information 1
- More Information 2
- Group series using mapper (dict or key function, apply given function to group, return result as series) or by a series of columns.
In [784]:
df= pd.read_csv('GroupBy.csv')
df
Out[784]:
Example-1¶
In [785]:
df.groupby('City').Temperature.sum()
Out[785]:
Example-2¶
In [786]:
df.groupby(['Province', 'City']).Temperature.sum()
Out[786]:
Example-3¶
In [787]:
df.groupby(['Province', 'City']).Temperature.sum().unstack()
Out[787]:
11.34 get_group¶
In [788]:
df= pd.read_csv('GroupBy.csv')
df1=df.groupby('City')
df2=df1.get_group('Lahore')
df2
Out[788]:
In [789]:
df2.mean()
Out[789]:
In [790]:
df2.describe()
Out[790]:
11.35 Transform Function in Pandas¶
- Call function producing a like-indexed NDFrame and return a NDFrame with the transformed values
- More Information
Example-1¶
In [791]:
df = pd.read_csv('chipotle.tsv',sep='\t')
df.head()
Out[791]:
In [792]:
df.dtypes
Out[792]:
In [793]:
# Preprocessing (Step-1)
df['item_price'] = df['item_price'].str.replace("$","")
In [794]:
df.dtypes
Out[794]:
In [795]:
# Preprocessing (Step-2)
df['item_price'] = pd.to_numeric(df['item_price'])
In [796]:
df.dtypes
Out[796]:
- Each order has an order_id and consists of one or more rows. To figure out the total price of an order, you sum the item_price for that order_id. For example, here's the total price of order number 1:
In [797]:
df[df.order_id == 1].item_price.sum()
Out[797]:
- If you wanted to calculate the total price of every order, you would groupby() order_id and then take the sum of item_price for each group:
In [798]:
df.groupby('order_id').item_price.sum().head()
Out[798]:
- However, you're not actually limited to aggregating by a single function such as sum().
- To aggregate by multiple functions, you use the agg() method and pass it a list of functions such as sum() and count():
In [799]:
df.groupby('order_id').item_price.agg(['sum', 'count']).head()
Out[799]:
In [800]:
len(df.groupby('order_id').item_price.sum())
Out[800]:
...is smaller than the input to the function:
In [801]:
len(df.item_price)
Out[801]:
- The solution is to use the transform() method, which performs the same calculation but returns output data that is the same shape as the input data:
In [802]:
df['total_price'] = df.groupby('order_id').item_price.transform('sum')
In [803]:
df.head()
Out[803]:
- As you can see, the total price of each order is now listed on every single line.
- That makes it easy to calculate the percentage of the total order price that each line represents:
In [804]:
df['percent_of_total'] = df.item_price / df.total_price
Example-2¶
In [805]:
df = pd.DataFrame({"measure": ["a","a","b","a","c","c"],
"aq": [10,20,30,20,30,50]})
df
Out[805]:
In [806]:
df["colour"] = (100.0 * df["aq"] /
df.groupby("measure")["aq"].transform(max))
df
Out[806]:
11.36 Remove duplicate rows in pandas¶
- More Information
- Return DataFrame with duplicate rows removed, optionally only considering certain columns
In [807]:
from IPython.display import YouTubeVideo
YouTubeVideo('ht5buXUMqkQ',width=900, height=500)
Out[807]:
In [808]:
df= pd.read_csv('DupDS.csv')
df
Out[808]:
identify duplicated rows¶
- More Information
- Return boolean Series denoting duplicate rows, optionally only considering certain columns
In [809]:
df.duplicated()
Out[809]:
In [810]:
df.duplicated().sum()
Out[810]:
Find duplicated rows¶
In [811]:
df.loc[df.duplicated(),:]
Out[811]:
In [812]:
df.loc[df.duplicated(keep='first'),:]
Out[812]:
In [813]:
df.loc[df.duplicated(keep='last'),:]
Out[813]:
In [814]:
df.loc[df.duplicated(keep=False),:]
Out[814]:
Shape of the Data Frame after drop duplicates¶
In [815]:
df.drop_duplicates().shape
Out[815]:
In [816]:
df.drop_duplicates(keep='first').shape
Out[816]:
In [817]:
df.drop_duplicates(keep='last').shape
Out[817]:
In [818]:
df.drop_duplicates(keep=False).shape
Out[818]:
Data Frame after drop duplicates¶
In [819]:
df.drop_duplicates()
Out[819]:
In [820]:
df.drop_duplicates(keep='first')
Out[820]:
In [821]:
df.drop_duplicates(keep='last')
Out[821]:
In [822]:
df.drop_duplicates(keep=False)
Out[822]:
11.37 Pandas Stack¶
In [823]:
from IPython.display import YouTubeVideo
YouTubeVideo('BUOy4RUUepg',width=900, height=500)
Out[823]:
- Stack the prescribed level(s) from columns to index.
- More Information
Example-1¶
In [824]:
df=pd.read_excel("Company.xlsx",header=[0,1],index_col=[0])
df
Out[824]:
In [825]:
df1=df.stack()
df1
Out[825]:
In [826]:
df2=df.stack(level=0)
df2
Out[826]:
11.38 Pandas Unstack¶
- More Information
- Pivot a level of the (necessarily hierarchical) index labels, returning a DataFrame having a new level of column labels whose inner-most level consists of the pivoted index labels.
In [827]:
df2.unstack()
Out[827]:
Example-2¶
In [828]:
df=pd.read_excel("Company1.xlsx",header=[0,1,2],index_col=[0])
df
Out[828]:
In [829]:
df1=df.stack()
df1
Out[829]:
In [830]:
df2=df.stack(level=0)
df2
Out[830]:
In [831]:
df3=df.stack(level=1)
df3
Out[831]:
In [832]:
df4=df.stack(level=2)
df4
Out[832]:
- by defalult level is most inner one.
11.39 Python Pandas replicate rows in dataframe¶
Example-1¶
In [833]:
df=pd.read_csv("rep.csv")
df
Out[833]:
In [834]:
pd.concat([df]*3)
Out[834]:
In [835]:
pd.concat([df]*3, ignore_index=True)
Out[835]:
Example-2¶
In [836]:
rep2 = df.loc[np.repeat(df.index.values,df.Rep)].reset_index(drop=True)
rep2
Out[836]:
In [837]:
rep2 = rep2.drop("Rep",axis=1).reset_index(drop=True)
rep2
Out[837]:
11.40 Data manipulations¶
Conditional rolling count¶
In [838]:
df= pd.read_csv('maybe.csv',header=0)
df
Out[838]:
In [839]:
df['count'] = df.groupby('Year').cumcount() + 0
df
Out[839]:
compound¶
- Return the compound percentage of the values for the requested axis
- More Information1
- More Information2
- So for columns a: [(0+1)(2+1)(4+1)]-1=[(1)(3)(5)]-1=15-1=14
- So for columns b: [(1+1)(3+1)(5+1)]-1=[(2)(4)(6)]-1=48-1=47
In [840]:
df = pd.DataFrame(np.arange(6).reshape(3,2), columns=['a', 'b'])
df
Out[840]:
In [841]:
df.compound()
Out[841]:
cumprod¶
- Return cumulative product over a DataFrame or Series axis
- More Information
In [842]:
df = pd.Series([2, 10, np.nan, 4, 3, 0, 1])
df.cumprod()
Out[842]:
cummax¶
- Return cumulative maximum over a DataFrame or Series axis.
- Returns a DataFrame or Series of the same size containing the cumulative maximum
- More Information
Example-1¶
In [843]:
df = pd.Series([2, np.nan, 5, -1, 0])
df
Out[843]:
- By default, NA values are ignored.
In [844]:
df.cummax()
Out[844]:
- To include NA values in the operation, use skipna=False
In [845]:
df.cummax(skipna=False)
Out[845]:
Example-2¶
In [846]:
df = pd.DataFrame([[2.0, 1.0],
[3.0, np.nan],
[1.0, 0.0]],
columns=list('AB'))
df
Out[846]:
- By default, iterates over rows and finds the maximum in each column. This is equivalent to axis=None or axis='index'
In [847]:
df.cummax()
Out[847]:
- To iterate over columns and find the maximum in each row, use axis=1
In [848]:
df.cummax(axis=1)
Out[848]:
cummin¶
- Return cumulative minimum over a DataFrame or Series axis.
- Returns a DataFrame or Series of the same size containing the cumulative minimum.
- More Information
Example-1¶
In [849]:
df = pd.Series([2, np.nan, 5, -1, 0])
df
Out[849]:
- By default, NA values are ignored.
In [850]:
df.cummin()
Out[850]:
- To include NA values in the operation, use skipna=False
In [851]:
df.cummin(skipna=False)
Out[851]:
Example-2¶
In [852]:
df = pd.DataFrame([[2.0, 1.0],
[3.0, np.nan],
[1.0, 0.0]],
columns=list('AB'))
- By default, iterates over rows and finds the maximum in each column. This is equivalent to axis=None or axis='index'
In [853]:
df.cummin()
Out[853]:
- To iterate over columns and find the maximum in each row, use axis=1.
In [854]:
df.cummin(axis=1)
Out[854]:
cumsum¶
- Return cumulative sum over a DataFrame or Series axis.
- Returns a DataFrame or Series of the same size containing the cumulative sum.
- More Information
Example-1¶
In [855]:
df = pd.Series([2, np.nan, 5, -1, 0])
df
Out[855]:
- By default, NA values are ignored.
In [856]:
df.cumsum()
Out[856]:
- To include NA values in the operation, use skipna=False
In [857]:
df.cumsum(skipna=False)
Out[857]:
Example-2¶
In [858]:
df = pd.DataFrame([[2.0, 1.0],
[3.0, np.nan],
[1.0, 0.0]],
columns=list('AB'))
df
Out[858]:
- By default, iterates over rows and finds the sum in each column. This is equivalent to axis=None or axis='index'.
In [859]:
df.cumsum()
Out[859]:
- To iterate over columns and find the sum in each row, use axis=1
In [860]:
df.cumsum(axis=1)
Out[860]:
Example-3¶
In [861]:
df= pd.read_csv('maybe.csv',header=0)
df
Out[861]:
In [862]:
df.iat[2, 1]=1983
In [863]:
df
Out[863]:
In [864]:
df['GD'] = (df['Year'] != df['Year'].shift(1)).astype(int).cumsum()
In [865]:
df['count'] = df.groupby('GD').cumcount() + 0
df
Out[865]:
expanding¶
- Provides expanding transformations.
- More Information
In [866]:
df = pd.DataFrame({'B': [0, 3, 2, 7, 4]})
df
Out[866]:
In [867]:
df.expanding(2).sum()
Out[867]:
rolling¶
- Provide rolling window calculations.
- More Information
In [868]:
df = pd.DataFrame({'B': [0, 3, 2, 7, 4]})
df
Out[868]:
In [869]:
df.rolling(2).sum()
Out[869]:
pct_change¶
- More Information
- Percentage change between the current and a prior element.
- Computes the percentage change from the immediately previous row by default.
- This is useful in comparing the percentage of change in a time series of elements.
In [870]:
df= pd.read_csv('AAPL.csv',parse_dates=['Date'])
df
Out[870]:
In [871]:
df['dchange']=100*df['Close'].pct_change()
df
Out[871]:
11.41 Data Frame Formatting in Pandas¶
In [872]:
from IPython.display import YouTubeVideo
YouTubeVideo('yiO43TQ4xvc',width=900, height=500)
Out[872]:
Display Rows Method in Pandas¶
In [873]:
df_who= pd.read_csv('WHO_csv.csv')
pd.get_option('display.max_rows')
Out[873]:
Example-1¶
In [874]:
df_who= pd.read_csv('WHO_csv.csv')
pd.set_option('display.max_rows',None)
df_who
Out[874]:
Example-2¶
In [875]:
pd.reset_option('all',silent=True)
import pandas as pd
df_who= pd.read_csv('WHO_csv.csv')
pd.set_option('display.max_rows',6)
df_who
Out[875]:
reset_option¶
In [876]:
pd.reset_option('display.max_rows')
df_who
#Method-2
#pd.reset_option('all',silent=True)
Out[876]:
colwidth¶
In [877]:
pd.reset_option('all',silent=True)
df_who= pd.read_csv('WHO_csv.csv')
pd.set_option('display.max_colwidth',10)
df_who.head()
Out[877]:
Display Precision¶
In [878]:
pd.reset_option('all',silent=True)
df_who= pd.read_csv('WHO_csv.csv')
pd.set_option('display.precision',1)
df_who.head()
Out[878]:
Display , in Float¶
In [879]:
pd.reset_option('all',silent=True)
df_who= pd.read_csv('WHO_csv.csv')
pd.set_option('display.float_format','{:,}'.format)
df_who.head()
Out[879]:
Style a DataFrame¶
- The previous trick is useful if you want to change the display of your entire notebook. However, a more flexible and powerful approach is to define the style of a particular DataFrame.
- Let's return to the stocks DataFrame:
In [880]:
df= pd.read_csv('Sale.txt',sep=",",parse_dates=['Date'])
df
Out[880]:
- We can create a dictionary of format strings that specifies how each column should be formatted
- And then we can pass it to the DataFrame's style.format() method:
In [881]:
df.style.format({'Symbol': str.lower,'Volume':'{:,}','Date':'{:%m/%d/%y}','Close':'${:.2f}'})
Out[881]:
- Notice that the Date is now in month-day-year format, the closing price has a dollar sign, and the Volume has commas.
- We can apply more styling by chaining additional methods
In [882]:
(df.style.format({'Symbol': str.lower,'Volume':'{:,}','Date':'{:%m/%d/%y}','Close':'${:.2f}'})
.hide_index()
.highlight_min(['Close','Volume'], color='red')
.highlight_max('Close', color='lightgreen')
)
Out[882]:
- We've now hidden the index, highlighted the minimum Close value in red, and highlighted the maximum Close value in green.
- Here's another example of DataFrame styling:
In [883]:
(df.style.format({'Symbol': str.lower,'Volume':'{:,}','Date':'{:%m/%d/%y}','Close':'${:.2f}'})
.hide_index()
.background_gradient(subset='Volume', cmap='Blues')
)
Out[883]:
- The Volume column now has a background gradient to help you easily identify high and low values.
- And here's one final example
In [884]:
(df.style.format({'Symbol': str.lower,'Volume':'{:,}','Date':'{:%m/%d/%y}','Close':'${:.2f}'})
.hide_index()
.bar('Volume', color='lightblue', align='zero')
.set_caption('Stock Prices from October 2016')
)
Out[884]:
- There's now a bar chart within the Volume column and a caption above the DataFrame.
- Note that there are many more options for how you can style your DataFrame
- More Information
11.42 Time Series Analysis¶
- Timeseries is a set of data points indexed in time order.
In [885]:
from IPython.display import YouTubeVideo
YouTubeVideo('yCgJGsg0Xa4',width=900, height=500)
Out[885]:
Data Type of Date¶
In [886]:
from IPython.display import YouTubeVideo
YouTubeVideo('r0s4slGHwzE',width=900, height=500)
Out[886]:
In [887]:
df= pd.read_csv('AAPL.csv')
In [888]:
df.head()
Out[888]:
In [889]:
type(df.Date[0])
Out[889]:
Solution¶
In [890]:
df= pd.read_csv('AAPL.csv',parse_dates=['Date'])
In [891]:
df.head()
Out[891]:
In [892]:
type(df.Date[0])
Out[892]:
Select Date Col to Index¶
In [893]:
df= pd.read_csv('AAPL.csv',parse_dates=['Date'],index_col='Date')
df.head()
Out[893]:
In [894]:
df.index
Out[894]:
partial index¶
Example-1¶
In [895]:
df['2018-01']
Out[895]:
Example-2¶
- Find Mean Price of Apple's Stock in Jan,2018
In [896]:
df['2018-01'].Close.mean()
Out[896]:
Example-3¶
In [897]:
df.loc['2018-01-29']
Out[897]:
Example-4¶
In [898]:
df['2018-01-15':'2018-01-20']
Out[898]:
Example-5¶
In [899]:
df['2018-01-15':'2018-01-20'].mean()
Out[899]:
Example-6¶
In [900]:
df['2018-01-15':'2018-01-20'].Close.mean()
Out[900]:
Resampling¶
- Convenience method for frequency conversion and resampling of time series.
- More Information
Example-1¶
- Find Monthly Average Price of Apple's Stock in 2018
- More Information
In [901]:
df.resample('M').mean()
Out[901]:
Example-2¶
In [902]:
df['Close'].resample('M').mean()
Out[902]:
Date Range¶
In [903]:
from IPython.display import YouTubeVideo
YouTubeVideo('A9c7hGXQ5A8',width=900, height=500)
Out[903]:
Example-1¶
In [904]:
df= pd.read_csv('AAPLwd.csv')
df.head()
Out[904]:
- Date column is missing in the data set
In [905]:
df1= pd.date_range(start="1/2/2018", end="1/31/2018",freq='B')
df1
Out[905]:
In [906]:
df.set_index(df1,inplace=True)
df
Out[906]:
Example-2¶
- Include wk end in the data set
- method='pad' : Fwd fill method (get the values for 'Sat' and 'Sun' from 'Fri')
In [907]:
df.asfreq('D',method='pad')
Out[907]:
Example-3¶
In [908]:
df.asfreq('W',method='pad')
Out[908]:
Example-4¶
In [909]:
df.asfreq('H',method='pad')
Out[909]:
Example-5¶
In [910]:
df= pd.date_range(start='1/1/2017',periods=72, freq='B')
df
Out[910]:
In [911]:
ts=pd.Series(np.random.randint(1,10,len(df)),index=df)
ts
Out[911]:
bdate_range¶
- Return a fixed frequency DatetimeIndex, with business day as the default frequency
- More Information
In [912]:
bd=pd.bdate_range(start='9/1/2019', end='9/16/2019')
bd
Out[912]:
timedelta_range¶
- Return a fixed frequency TimedeltaIndex, with day as the default frequency
- More Information
In [913]:
tr=pd.timedelta_range(start='1 day', periods=4)
tr
Out[913]:
In [914]:
tr=pd.timedelta_range(start='1 day', periods=4,closed='right')
tr
Out[914]:
In [915]:
tr=pd.timedelta_range(start='1 day', periods=4,closed='left')
tr
Out[915]:
In [916]:
tr=pd.timedelta_range(start='1 day', end='2 days', freq='6H')
tr
Out[916]:
interval_range¶
- Return a fixed frequency IntervalIndex
- More Information
In [917]:
ir=pd.interval_range(start=0, end=5)
ir
Out[917]:
In [918]:
ir= pd.interval_range(start=pd.Timestamp('2017-01-01'),
end=pd.Timestamp('2017-01-04'))
ir
Out[918]:
In [919]:
ir=pd.interval_range(start=0, periods=4, freq=1.5)
ir
Out[919]:
In [920]:
ir= pd.interval_range(start=pd.Timestamp('2019-01-01'),
periods=3, freq='MS')
ir
Out[920]:
In [921]:
ir=pd.interval_range(start=0, end=6, periods=4)
ir
Out[921]:
In [922]:
ir=pd.interval_range(end=5, periods=4, closed='both')
ir
Out[922]:
CustomBusinessDay¶
In [923]:
from IPython.display import YouTubeVideo
YouTubeVideo('Fo0IMzfcnQE',width=900, height=500)
Out[923]:
US Holidays¶
In [924]:
df= pd.read_csv('AAPLus.csv')
df.head()
Out[924]:
In [925]:
df1= pd.date_range(start="7/1/2018", end="7/31/2018",freq='B')
df1
Out[925]:
- 2018-07-04 US National Holiday
In [926]:
from pandas.tseries.holiday import USFederalHolidayCalendar
from pandas.tseries.offsets import CustomBusinessDay
usb=CustomBusinessDay(calendar=USFederalHolidayCalendar())
usb
Out[926]:
In [927]:
df1= pd.date_range(start="7/1/2018", end="7/31/2018",freq=usb)
df1
Out[927]:
In [928]:
df.set_index(df1,inplace=True)
df
Out[928]:
Pakistan Holidays¶
In [929]:
df= pd.read_csv('AAPLpk.csv')
df
Out[929]:
In [930]:
df1= pd.date_range(start="8/1/2018", end="8/31/2018",freq='B')
df1
Out[930]:
In [931]:
'''
class USFederalHolidayCalendar(AbstractHolidayCalendar):
"""
US Federal Government Holiday Calendar based on rules specified by:
https://www.opm.gov/policy-data-oversight/
snow-dismissal-procedures/federal-holidays/
"""
rules = [
Holiday('New Years Day', month=1, day=1, observance=nearest_workday),
USMartinLutherKingJr,
USPresidentsDay,
USMemorialDay,
Holiday('July 4th', month=7, day=4, observance=nearest_workday),
USLaborDay,
USColumbusDay,
Holiday('Veterans Day', month=11, day=11, observance=nearest_workday),
USThanksgivingDay,
Holiday('Christmas', month=12, day=25, observance=nearest_workday)
]
'''
Out[931]:
In [932]:
# Pakistan Holidays Calendar Code:
# Import Required Lib:
from pandas.tseries.holiday import AbstractHolidayCalendar, nearest_workday, Holiday
class PakistanFederalHolidayCalendar(AbstractHolidayCalendar):
"""
Pakistan Federal Government Holiday Calendar based on rules specified by:
https://publicholidays.pk/2018-dates/
"""
rules = [
Holiday('Independence Day', month=8, day=14)
]
pakc=CustomBusinessDay(calendar=PakistanFederalHolidayCalendar())
pakc
Out[932]:
In [933]:
df1= pd.date_range(start="8/1/2018", end="8/31/2018",freq=pakc)
df1
Out[933]:
In [934]:
df.set_index(df1,inplace=True)
df
Out[934]:
Example¶
- Pandas Time Series Analysis:Holidays (For holidays that occur on fixed dates (e.g., US Memorial Day or July 4th) an observance rule determines when that holiday is observed if it falls on a weekend or some other non-observed day)
In [935]:
df= pd.read_csv('AAPLusaeed.csv')
df
Out[935]:
In [936]:
### Umer Saeed Birthday Calendar Code:
### Import Required Lib:
from pandas.tseries.holiday import AbstractHolidayCalendar, nearest_workday, Holiday
class UmerSaeedBirthdayCalendar(AbstractHolidayCalendar):
rules = [
Holiday('Umer Saeed Birthday', month=1, day=7,observance=nearest_workday),
]
ubc=CustomBusinessDay(calendar=UmerSaeedBirthdayCalendar())
ubc
Out[936]:
In [937]:
df1= pd.date_range(start="1/1/2018", end="1/31/2018",freq=ubc)
df1
Out[937]:
In [938]:
df.set_index(df1,inplace=True)
df
Out[938]:
In [939]:
from IPython.display import Image
Image(filename='Rules.png')
Out[939]:
Custome week end¶
In [940]:
ksa=CustomBusinessDay(weekmask='Sun Mon Tue Wed Thu')
In [941]:
df= pd.date_range(start="1/1/2018", end="1/31/2018",freq=ksa)
df
Out[941]:
In [942]:
ksa=CustomBusinessDay(weekmask='Sun Mon Tue Wed Thu',holidays=['2018-01-08'])
In [943]:
df= pd.date_range(start="1/1/2018", end="1/31/2018",freq=ksa)
df
Out[943]:
to_datetime¶
In [944]:
from IPython.display import YouTubeVideo
YouTubeVideo('igWjq3jtLYI',width=900, height=500)
Out[944]:
- Most common problem in data analysis is lack of uniformity in the structure of input data.
In [945]:
from IPython.display import Image
Image(filename='date_format.png')
Out[945]:
Example-1¶
In [946]:
df= pd.read_csv('date_data.txt')
df
Out[946]:
In [947]:
df.dtypes
Out[947]:
In [948]:
df = df.apply(pd.to_datetime)
df
Out[948]:
In [949]:
df.dtypes
Out[949]:
Example-2¶
In [950]:
df= pd.read_csv('date_data_time.txt')
df
Out[950]:
In [951]:
df.dtypes
Out[951]:
In [952]:
df = df.apply(pd.to_datetime)
df
Out[952]:
Example-3¶
In [953]:
from IPython.display import Image
Image(filename='Format_D_U_E.png')
Out[953]:
- 5th Jan in Europe, however pd.to_datetime convert to 1st May
In [954]:
pd.to_datetime('5/1/2018')
Out[954]:
Solution¶
In [955]:
pd.to_datetime('5/1/2018',dayfirst=True)
Out[955]:
In [956]:
pd.to_datetime('5$1$2018',format='%d$%m$%Y')
Out[956]:
Error Handling¶
In [957]:
df=['2019-07-01','2019-07-02','2019-07-03','2019-07-04','2019-07-05','abc']
In [958]:
pd.to_datetime(df)
Solution-1¶
In [959]:
pd.to_datetime(df,errors='ignore')
Out[959]:
Solution-2¶
In [960]:
pd.to_datetime(df,errors='coerce')
Out[960]:
Epoch unix time¶
- Epoch (unix time) is number of seconds that have passed since Jan 1, 1970 00:00:00 UTC
In [961]:
from IPython.display import Image
Image(filename='Epoch_Time.png')
Out[961]:
In [962]:
t=1570434845
# t is in seconds
# unit : string, default 'ns'
df=pd.to_datetime([t],unit='s')
df
Out[962]:
In [963]:
df.view('int64')
Out[963]:
to_timedelta¶
- Convert argument to timedelta.
- Timedeltas are absolute differences in times, expressed in difference units (e.g. days, hours, minutes, seconds).
- This method converts an argument from a recognized timedelta format / value into a Timedelta type.
- More Information
In [964]:
td = pd.Timedelta('3 days 06:05:01.000000111')
td
Out[964]:
In [965]:
td.seconds
Out[965]:
In [966]:
td = pd.Timedelta(133, unit ='s')
td
Out[966]:
In [967]:
td.seconds
Out[967]:
Period¶
Frequency as a Year¶
Method-1¶
In [968]:
a= pd.Period('2019')
a
Out[968]:
In [969]:
a.start_time
Out[969]:
In [970]:
a.end_time
Out[970]:
Method-2¶
In [971]:
a=pd.Period('2019',freq='A')
a
Out[971]:
In [972]:
a.start_time
Out[972]:
In [973]:
a.end_time
Out[973]:
Arithmetic Functions¶
Example-1¶
In [974]:
a= pd.Period('2019')
a=a+1
In [975]:
a.start_time
Out[975]:
In [976]:
a.end_time
Out[976]:
Example-2¶
In [977]:
a=pd.Period('2019',freq='A')
a=a+1
a
Out[977]:
In [978]:
a.start_time
Out[978]:
In [979]:
a.end_time
Out[979]:
Frequency as a Month¶
Method-1¶
In [980]:
m=pd.Period('2019',freq='M')
m
Out[980]:
In [981]:
m.start_time
Out[981]:
In [982]:
m.end_time
Out[982]:
Method-2¶
In [983]:
m=pd.Period('2019-10')
m
Out[983]:
In [984]:
m.start_time
Out[984]:
In [985]:
m.end_time
Out[985]:
Arithmetic Functions¶
Example-1¶
In [986]:
m=pd.Period('2019',freq='M')
m=m+1
m
Out[986]:
In [987]:
m.start_time
Out[987]:
In [988]:
m.end_time
Out[988]:
Example-2¶
In [989]:
m=pd.Period('2019-12')
m=m+1
m
Out[989]:
In [990]:
m.start_time
Out[990]:
In [991]:
m.end_time
Out[991]:
Frequency as a Day¶
Method-1¶
In [992]:
d=pd.Period('2019-10-07')
d
Out[992]:
In [993]:
d.start_time
Out[993]:
In [994]:
d.end_time
Out[994]:
Method-2¶
In [995]:
d=pd.Period('2019-10-07',freq='D')
d
Out[995]:
In [996]:
d.start_time
Out[996]:
In [997]:
d.end_time
Out[997]:
Arithmetic Functions¶
Example-1¶
In [998]:
d=pd.Period('2018-02-28')
d=d+1
d
Out[998]:
In [999]:
d.start_time
Out[999]:
In [1000]:
d.end_time
Out[1000]:
Example-2¶
In [1001]:
d=pd.Period('2020-02-28',freq='D')
d=d+1
d
Out[1001]:
In [1002]:
d.start_time
Out[1002]:
In [1003]:
d.end_time
Out[1003]:
Leap Year¶
In [1004]:
pd.Period('2018-03').is_leap_year
Out[1004]:
In [1005]:
pd.Period('2018').is_leap_year
Out[1005]:
In [1006]:
pd.Period('2020-03').is_leap_year
Out[1006]:
In [1007]:
pd.Period('2020-10-10').is_leap_year
Out[1007]:
Frequency as an Hour¶
Method-1¶
In [1008]:
h=pd.Period('2020-02-28',freq='H')
h
Out[1008]:
In [1009]:
h.start_time
Out[1009]:
In [1010]:
h.end_time
Out[1010]:
Method-2¶
In [1011]:
h=pd.Period('2020-02-28 14:00:00',freq='H')
h
Out[1011]:
In [1012]:
h.start_time
Out[1012]:
In [1013]:
h.end_time
Out[1013]:
Arithmetic Functions¶
Example-1¶
In [1014]:
h=pd.Period('2020-02-28',freq='H')
h=h+1
h
Out[1014]:
In [1015]:
h.start_time
Out[1015]:
In [1016]:
h.end_time
Out[1016]:
Example-2¶
In [1017]:
h=pd.Period('2020-02-28 23:00:00',freq='H')
h=h+1
h
Out[1017]:
In [1018]:
h.start_time
Out[1018]:
In [1019]:
h.end_time
Out[1019]:
Frequency as a Quarter¶
Method-1¶
In [1020]:
q=pd.Period('2018',freq='Q')
q
Out[1020]:
In [1021]:
q.start_time
Out[1021]:
In [1022]:
q.end_time
Out[1022]:
Method-2¶
In [1023]:
q=pd.Period('2018-4',freq='Q')
q
Out[1023]:
In [1024]:
q.start_time
Out[1024]:
In [1025]:
q.end_time
Out[1025]:
Method-3¶
In [1026]:
q=pd.Period('2018Q3',freq='Q')
q
Out[1026]:
In [1027]:
q.start_time
Out[1027]:
In [1028]:
q.end_time
Out[1028]:
Arithmetic Functions¶
Example-1¶
In [1029]:
q=pd.Period('2018',freq='Q')
q=q+1
q
Out[1029]:
In [1030]:
q.start_time
Out[1030]:
In [1031]:
q.end_time
Out[1031]:
Example-2¶
In [1032]:
q=pd.Period('2018-4',freq='Q')
q=q+1
q
Out[1032]:
In [1033]:
q.start_time
Out[1033]:
In [1034]:
q.end_time
Out[1034]:
Example-3¶
In [1035]:
q=pd.Period('2018Q3',freq='Q')
q=q+1
q
Out[1035]:
In [1036]:
q.start_time
Out[1036]:
In [1037]:
q.end_time
Out[1037]:
customized Quarter¶
In [1038]:
qc=pd.Period('2018', freq='Q-Jan')
qc
Out[1038]:
In [1039]:
qc.start_time
Out[1039]:
In [1040]:
qc.end_time
Out[1040]:
Arithmetic Functions¶
Example-1¶
In [1041]:
q1=pd.Period('2018', freq='Q-Jan')
In [1042]:
q1.start_time
Out[1042]:
In [1043]:
q1.end_time
Out[1043]:
In [1044]:
q2=pd.Period('2016', freq='Q-Jan')
In [1045]:
q2.start_time
Out[1045]:
In [1046]:
q2.end_time
Out[1046]:
In [1047]:
q3=q1-q2
q3
Out[1047]:
Example-2¶
In [1048]:
q1=pd.Period('2018', freq='Q-Nov')
q2=pd.Period('2016', freq='Q-Nov')
q3=q1-q2
q3
Out[1048]:
to_period¶
- More Information
- The function basically converts DatetimeIndex to PeriodIndex.
In [1049]:
df= pd.read_csv('AAPL.csv',parse_dates=["Date"],index_col="Date")
df
Out[1049]:
In [1050]:
df.to_period('Q')
Out[1050]:
offsets¶
Example-1¶
In [1051]:
df= pd.read_csv('AAPL.csv',parse_dates=['Date'])
df.head()
Out[1051]:
In [1052]:
df["NEW_DATE"] = df['Date'] - pd.offsets.DateOffset(years=1)
df.head()
Out[1052]:
Example-2¶
In [1053]:
df= pd.read_csv('AAPL.csv',parse_dates=['Date'])
df["NEW_DATE"] = df['Date'] + pd.offsets.Day(10)
df.head()
Out[1053]:
Example-3¶
In [1054]:
df= pd.read_csv('AAPL.csv',parse_dates=['Date'])
df["NEW_DATE"] = df['Date'] + pd.offsets.BDay(10)
df.head()
Out[1054]:
Example-4¶
In [1055]:
h=pd.Period('2018-12-15 20:00:00',freq='H')
h=h+pd.offsets.Hour(1)
h
Out[1055]:
In [1056]:
h.start_time
Out[1056]:
In [1057]:
h.end_time
Out[1057]:
Example-5¶
In [1058]:
h=pd.Period('2018-12-15 20:00:00',freq='H')
h=h+pd.offsets.Hour(-22)
h
Out[1058]:
In [1059]:
h.start_time
Out[1059]:
In [1060]:
h.end_time
Out[1060]:
asfreq Function¶
- Convert TimeSeries to specified frequency.
- More Information
Example-1¶
In [1061]:
q=pd.Period('2018-5',freq='Q')
In [1062]:
q.start_time
Out[1062]:
In [1063]:
q.end_time
Out[1063]:
In [1064]:
q=q.asfreq('M',how='start')
In [1065]:
q.start_time
Out[1065]:
In [1066]:
q.end_time
Out[1066]:
Example-2¶
In [1067]:
q=pd.Period('2018-5',freq='Q')
In [1068]:
q.start_time
Out[1068]:
In [1069]:
q.end_time
Out[1069]:
In [1070]:
q=q.asfreq('M',how='end')
In [1071]:
q.start_time
Out[1071]:
In [1072]:
q.end_time
Out[1072]:
PeriodIndex¶
In [1073]:
from IPython.display import YouTubeVideo
YouTubeVideo('3l9YOS4y24Y',width=900, height=500)
Out[1073]:
Example-1¶
In [1074]:
df=pd.period_range('2011','2018',freq='Q')
df
Out[1074]:
Example-2¶
In [1075]:
df=pd.period_range('2011','2018',freq='Q-Jan')
df
Out[1075]:
In [1076]:
df[0].start_time
Out[1076]:
In [1077]:
df[0].end_time
Out[1077]:
Example-3¶
In [1078]:
df= pd.read_csv('ind_data.csv')
df
Out[1078]:
In [1079]:
df.set_index('idx',inplace=True)
In [1080]:
df
Out[1080]:
In [1081]:
df.index
Out[1081]:
In [1082]:
df.index= pd.PeriodIndex(df.index,freq='Q')
In [1083]:
df.index
Out[1083]:
In [1084]:
df['2012']
Out[1084]:
In [1085]:
df['2011':'2012']
Out[1085]:
Example-4¶
In [1086]:
df= pd.read_csv('ts_ds.csv')
df
Out[1086]:
Required Oputput¶
In [1087]:
from IPython.display import Image
Image(filename='Required_Output.png')
Out[1087]:
In [1088]:
df.set_index('LineItem', inplace = True)
In [1089]:
df
Out[1089]:
In [1090]:
df=df.T
df
Out[1090]:
In [1091]:
df.index
Out[1091]:
In [1092]:
df.index= pd.PeriodIndex(df.index,freq='Q-Jan')
In [1093]:
df['Start Date']=df.index.map(lambda x: x.start_time)
df['End Date']=df.index.map(lambda x: x.end_time.floor("H"))
df
Out[1093]:
In [1094]:
# Day Name
df['DayName']=df['Start Date'].dt.weekday_name
# Get day of the month that a Period falls on
df['dayc']=df['Start Date'].dt.day
# Get Month Name
df['month']=df['Start Date'].dt.month
# Return the day of the year.
df['daynumber']=df['Start Date'].dt.dayofyear
# Get quarter Number
df['quarterernumb']=df['Start Date'].dt.quarter
# Day of the week the period lies in, with Monday=0 and Sunday=6.
df['dow']=df['Start Date'].dt.dayofweek
# Get the total number of days in the month that this period falls on.
df['dim']=df['Start Date'].dt.days_in_month
# year
df['yeartest']=df['Start Date'].dt.year
# Get the week of the year on the given Period.
df['weekn']=df['Start Date'].dt.week
df
Out[1094]:
TimeZone¶
In [1095]:
from IPython.display import Image
Image(filename='TimeZone.png')
Out[1095]:
- Two Types of DateTime objects in Python:
- 1) Naive (No timezone awareness)
- 2) Time Zone aware datetime
In [1096]:
from IPython.display import YouTubeVideo
YouTubeVideo('9IW2GIJajLs',width=900, height=500)
Out[1096]:
In [1097]:
from pytz import all_timezones
In [1098]:
all_timezones
Out[1098]:
Example-1¶
In [1099]:
df=pd.read_csv('TZH.csv',parse_dates=['Date Time'])
df
Out[1099]:
In [1100]:
df.set_index('Date Time', inplace = True)
In [1101]:
df
Out[1101]:
In [1102]:
df.index
Out[1102]:
In [1103]:
df=df.tz_localize(tz='Asia/Istanbul')
In [1104]:
df
Out[1104]:
In [1105]:
df.index
Out[1105]:
- Convert tz-aware axis to target time zone.
- More Information
Example-2¶
In [1106]:
df=df.tz_convert(tz='Asia/Karachi')
df
Out[1106]:
In [1107]:
df.index
Out[1107]:
In [1108]:
df=df.tz_convert(tz='dateutil/Europe/Berlin')
df
Out[1108]:
Arithmetic Functions¶
Data Frame-1¶
In [1109]:
pakistan= pd.read_csv('Karachi.csv',parse_dates=['Date'])
pakistan.head()
Out[1109]:
In [1110]:
pakistan.set_index('Date', inplace = True)
In [1111]:
pakistan.head()
Out[1111]:
In [1112]:
pakistan.index
Out[1112]:
- Localize tz-naive TimeSeries to target time zone.
- More Information
In [1113]:
pakistan=pakistan.tz_localize(tz='Asia/Karachi')
pakistan
Out[1113]:
In [1114]:
pakistan.index
Out[1114]:
Data Frame-2¶
In [1115]:
turki= pd.read_csv('Istanbul.csv',parse_dates=['Date'])
turki.head()
Out[1115]:
In [1116]:
turki.set_index('Date', inplace = True)
In [1117]:
turki.head()
Out[1117]:
In [1118]:
turki.index
Out[1118]:
In [1119]:
turki=turki.tz_localize(tz='Asia/Istanbul')
In [1120]:
turki.index
Out[1120]:
In [1121]:
turki
Out[1121]:
In [1122]:
f_res=pakistan+turki
f_res
Out[1122]:
tshift¶
- Shift the time index, using the index’s frequency if available.
- More Information
In [1123]:
df= pd.read_csv('egg_price.csv',parse_dates=['Date'],index_col='Date')
df
Out[1123]:
In [1124]:
df.tshift(1)
Out[1124]:
In [1125]:
df.tshift(-1)
Out[1125]:
first¶
- first: Convenience method for subsetting initial periods of time series data based on a date offset.
- More Information
In [1126]:
i = pd.date_range('2018-04-09', periods=4, freq='2D')
ts = pd.DataFrame({'A': [1,2,3,4]}, index=i)
ts
Out[1126]:
- Get the rows for the first 3 days:
In [1127]:
ts.first('3D')
Out[1127]:
Notice the data for 3 first calender days were returned, not the first 3 days observed in the dataset, and therefore data for 2018-04-13 was not returned.
last¶
- last: Convenience method for subsetting final periods of time series data based on a date offset.
- More Information
In [1128]:
i = pd.date_range('2018-04-09', periods=4, freq='2D')
ts = pd.DataFrame({'A': [1,2,3,4]}, index=i)
ts
Out[1128]:
In [1129]:
ts.last('3D')
Out[1129]:
- Notice the data for 3 last calender days were returned, not the last 3 observed days in the dataset, and therefore data for 2018-04-11 was not returned.
between_time¶
Example-1¶
In [1130]:
df= pd.read_csv('Cell_H_O.csv',index_col="Time",parse_dates=["Time"])
df.head()
Out[1130]:
In [1131]:
df.between_time('6:00','8:00')
Out[1131]:
Example-2¶
In [1132]:
df= pd.read_csv('Cell_H.csv',index_col="Time",parse_dates=["Time"])
df.head()
Out[1132]:
In [1133]:
df.between_time('6:00','8:00')
Out[1133]:
at_time¶
Example-1¶
In [1134]:
df= pd.read_csv('Cell_H_O.csv',index_col="Time",parse_dates=["Time"])
df.head()
Out[1134]:
In [1135]:
df.at_time('6:00')
Out[1135]:
Example-2¶
In [1136]:
df= pd.read_csv('Cell_H.csv',index_col="Time",parse_dates=["Time"])
df.head()
Out[1136]:
In [1137]:
df.at_time('6:00')
Out[1137]:
More of your pandas questions answered¶
In [1138]:
from IPython.display import YouTubeVideo
YouTubeVideo('oH3wYKvwpJ8',width=900, height=500)
Out[1138]:
MultiIndex in pandas¶
In [1139]:
from IPython.display import YouTubeVideo
YouTubeVideo('tcRGa2soc-c',width=900, height=500)
Out[1139]:
new time-saving tricks in pandas¶
In [1140]:
from IPython.display import YouTubeVideo
YouTubeVideo('-NbY7E9hKxk',width=900, height=500)
Out[1140]:
In [1141]:
from IPython.display import YouTubeVideo
YouTubeVideo('te5JrSCW-LY',width=900, height=500)
Out[1141]:
In [1142]:
from IPython.display import YouTubeVideo
YouTubeVideo('CWRKgBtZN18',width=900, height=500)
Out[1142]:
In [1143]:
from IPython.display import YouTubeVideo
YouTubeVideo('RlIiVeig3hc',width=900, height=500)
Out[1143]:
Shifting and Lagging¶
In [1144]:
from IPython.display import YouTubeVideo
YouTubeVideo('0lsmdNLNorY',width=900, height=500)
Out[1144]:
get¶
- More Information
- Extract element from each component at specified position.
- Extract element from lists, tuples, or strings in each element in the Series/Index.
In [1145]:
df_who= pd.read_csv('WHO_csv.csv',dtype = {"Region" : "category"})
df_who.head()
Out[1145]:
In [1146]:
df_who['Country0']=df_who['Country'].str.get(0)
df_who.head()
Out[1146]: